Amazon RDS - SQL Server - Master User Privileges

Question

I created an RDS instance with SQL Server Express Edition on it. The instance setup was all fine, and I also succeeded connecting to it.

But once connected using the master user, I cannot create any database, schema or table on the server. It keeps saying that the user does not have permission to perform the operation.

What could I be missing? Shouldn't the master user be having all the privileges by default? If not, how should I proceed?

Thanks for any help!

Edit:

This is what I'm trying to run:

CREATE TABLE [dbo].[wt_category] (
    [sys_id] bigint NOT NULL IDENTITY(1,1) ,
    [sys_timestamp] timestamp NOT NULL ,
    [country] varchar(5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [days] date NOT NULL ,
    [pages] varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT NULL ,
    [visits] int NULL DEFAULT NULL ,
    [page_impressions] int NULL DEFAULT NULL ,
    [visits_w_product] int NULL DEFAULT NULL ,
    [products] varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    PRIMARY KEY ([sys_id])
)

and I get this error:

[Err] 42000 - [SQL Server]CREATE TABLE permission denied in database 'rdsadmin'.

Answer 1

The error message indicates your user does not have permission to create a new table in the database "rdsadmin".

Check to see if the "rdsadmin" database exists, and has the correct privileges for the user you're trying to execute the commands as.

Troubleshooting:

For SQL Server you probably want someting like this:

Add the current admin user to SQL Server Express 2008

CREATE LOGIN [domain\username] FROM WINDOWS;
GO
EXEC sp_addsrvrolemember 'domain\username', 'sysadmin';
GO

For MySQL:

• rdsadmin is the admin user that gets created with RDS and has all global privileges (on localhost only). This is only for use by AWS/RDS, not by you.
• when I created a db/user in the RDS setup wizard my user 'test' was created, this had most global privileges (on %).

None of these accounts had any specific privileges granted on any database. You should explicitly grant privileges as you require them.

I would generally do this by logging in (either in terminal or by using a GUI like Sequel Pro) and executing the required privilege. In this example i would log into mysql with my 'test' user.

Once logged in, you can run the required MySQL commands:

http://kb.mediatemple.net/questions/788/HOWTO%3A+GRANT+privileges+in+MySQL#dv or http://library.linode.com/databases/mysql/arch-linux have examples of this, but here is a quick example:

CREATE DATABASE testdb;
CREATE USER 'testuser'@localhost IDENTIFIED BY 'CHANGEME';
GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@localhost;

Of course you will have to substitute with the proper database/user-name/password (you can skip db/user create commands if the db or user already exists).

You may want to grant a privilege only on localhost, or something different depending on what you're trying to achieve.

Answer 2

On an Amazon RDS SQL Server instance, the 'rdsadmin' database is used by the Amazon RDS rdsa user for instance management. It will appear as the only non-system database on a new RDS instance. While the master user has privileges to perform most actions on the server, you will not be able to modify this database, nor should you actually need to. More information on the limitations of the RDS master user is available here: SQL Server on Amazon RDS

In order to create a table, you will need to create a new database for your data first, and then you can create your table within that new database.

The following script gives an example of creating a DB, creating a table, inserting, selecting, and then dropping the table and DB. Using the "master" account login that you created when launching the RDS, you should have the privs to run this script with no problems.

USE master;

CREATE DATABASE NewDB;
GO

USE NewDB;

CREATE TABLE NewTable (Msg TEXT);

INSERT INTO NewTable (Msg) VALUES ('THIS SHOULD WORK');

SELECT * FROM NewTable;

DROP TABLE NewTable;

USE master;

DROP DATABASE NewDB;