CODEWITHSUNDEEP
sql-serveramazon-web-servicespermissionsrelational-databaseamazon-rds
Buchannon
Buchannon

Reputation: 1691

AWS RDS - Microsoft SQL Server Express - cannot add users with permissions to see/create databases

I created a SQL Server Express on my Amazon Web Services account. During this process I had to create a master username/password. With these credentials I'm able to create and see any database on the server, as well as other logins.

I created a separate login, but according to AWS documentation, it wont let me assign any of the following roles:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html The following server-level roles are not currently available in Amazon RDS:

bulkadmin
dbcreator
diskadmin
securityadmin
serveradmin
sysadmin

How can I create separate user accounts on this database, with full access to the database? So far any new user created cannot create a DB or see any existing data.

Upvotes: 2

Views: 5132

Answers (1)

John Hanley
John Hanley

Reputation: 81336

Here is a sequence of commands that you can run in Microsoft SQL Server Management Studio to create a user that has the permissions that you require.

Replace NewUserName with the new user's name. Replace myPassword with the new password.

USE master;
 GO

CREATE LOGIN [NewUserName]
 WITH PASSWORD = N'myPassword',
 CHECK_POLICY = OFF,
 CHECK_EXPIRATION = OFF;
 GO

GRANT ALTER ANY CONNECTION TO [NewUserName] WITH GRANT OPTION;
 GRANT ALTER ANY LINKED SERVER TO [NewUserName] WITH GRANT OPTION;
 GRANT ALTER ANY LOGIN TO [NewUserName] WITH GRANT OPTION;
 GRANT ALTER SERVER STATE TO [NewUserName] WITH GRANT OPTION;
 GRANT ALTER TRACE TO [NewUserName] WITH GRANT OPTION;
 GRANT CREATE ANY DATABASE TO [NewUserName] WITH GRANT OPTION;
 GRANT VIEW ANY DATABASE TO [NewUserName] WITH GRANT OPTION;
 GRANT VIEW ANY DEFINITION TO [NewUserName] WITH GRANT OPTION;
 GRANT VIEW SERVER STATE TO [NewUserName] WITH GRANT OPTION;

EXEC sp_MSforeachdb '
 DECLARE @name VARCHAR(500)
 SET @name=''?''
 IF DB_ID(@name) > 5
 BEGIN
 USE [?]
 CREATE USER NewUserName FOR LOGIN NewUserName;
 EXEC sp_addrolemember ''db_owner'',''NewUserName''
 END'

Upvotes: 9

Related Questions