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,300 points
643 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
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,140 points

Related Questions In AWS

0 votes
1 answer
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
20 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,140 points
122 views
0 votes
1 answer

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.