Creating users for databases on aws rds

0 votes

I have an RDS instance spun up in AWS, running SQL Server 2012. I am able to login and create new databases fine (Through SSMS and sqlcmd.exe).

The issue I'm facing is that I cannot create users for each of my application databases due to not having sufficient privileges on "godmode" account setup through the RDS creation process.

I have > 5 applications with their databases on this RDS instance and want to set it up so that each application has its own login and is a user ONLY to its own database.

I can create a login, but I cannot assign that login as a user to a database. Is this something that cannot be done? I'm aware that RDS has restrictions, but this seems like something that should be possible. My use case cannot be all that unique!

I'm attempting to set up the users like so:

DECLARE @Username nvarchar(255) = 'test1';
DECLARE @Password nvarchar(255) = 'sUp3rS3crEt';

USE [Application1] 

IF NOT EXISTS 
    (SELECT name  
     FROM master.sys.server_principals
     WHERE name = @Username)
BEGIN
    EXEC sp_addlogin @Username, @Password
END

BEGIN
EXEC sp_adduser @Username, @Username, 'db_owner'
END

which fails on sp_adduser with

Msg 15247, Level 16, State 1, Procedure sp_adduser, Line 35 [Batch Start Line 0] 
User does not have permission to perform this action.

Attempting to manually create a user through SSMS also fails on permission issues.

Is this actual expected behavior of RDS with Sql Server 2012?

Oct 1, 2018 in AWS by bug_seeker
• 15,350 points
1,292 views

1 answer to this question.

0 votes

I had this issue too, but in my case the solution was to ensure that you call USE [Application1]instead of USE [master] before creating the Login (which I realise the opening post already has, but for posterity's sake I'll leave this answer here).

i.e.

USE [Application1] 

IF NOT EXISTS 
    (SELECT name  
     FROM master.sys.server_principals
     WHERE name = @Username)
BEGIN
    EXEC sp_addlogin @Username, @Password
END
answered Oct 1, 2018 by Priyaj
• 56,900 points

Related Questions In AWS

0 votes
1 answer

How can I just increase the size of my root disk on AWS EC2 for use with Elastic Beanstalk?

This can be done using the following ...READ MORE

answered Nov 12, 2018 in AWS by Archana
• 5,560 points
230 views
0 votes
1 answer
0 votes
1 answer

Change password policy for IAM users - AWS

Hey @Himanshu, follow these steps: Go to IAM ...READ MORE

answered Apr 9 in AWS by Fatima
40 views
+1 vote
2 answers
0 votes
1 answer

Migration on-premise Postgresql into AWS RDS

Actually the solution purposed works. But there's ...READ MORE

answered Aug 24, 2018 in AWS by Priyaj
• 56,900 points
293 views
0 votes
1 answer