Amazon RDS – SQL Server – Master User Privileges

amazon-rdsamazon-web-services

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'.

Best Answer

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.

Related Topic