Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
Can you please post specifics and what you are trying to do, what what errors you get? –  Drew Khoury Apr 7 '13 at 8:22
    
@DrewKhoury - Please see the edit. Thanks. –  Nirmal Apr 7 '13 at 9:24

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks Drew. But the actual question is why the master user is being denied permission. Is there a config that needs to be done in RDS interface? –  Nirmal Apr 7 '13 at 9:41
    
I'm not completely sure. I usually use the master user to create specific users for whatever actions i am after, and those users get the privileges they need to do the job. –  Drew Khoury Apr 7 '13 at 9:45
    
I've done a test and updated my answer. While your user should have all GLOBAL privileges you will need to make sure you assign them the correct privileges per database/host. –  Drew Khoury Apr 7 '13 at 10:24
    
You are amazing! You somehow showed me the route and now I got everything to work. Thank you so much! –  Nirmal Apr 7 '13 at 12:12
1  
done. sorry about that I live in a mysql world so I forgot other dbs existed :p –  Drew Khoury Apr 10 '13 at 13:52

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;
share|improve this answer
    
Nope. Creating a new database works, but trying to do absolutely anything with it including creating a table results in The SELECT permission was denied on the object 'schemas', database 'mssqlsystemresource', shcmea 'sys' –  The Muffin Man May 29 at 20:35
    
Can you try running the SQL that I just added to my answer and see if it works for you? –  DAC May 30 at 17:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.