Creating users for databases on aws rds

Question

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?

Answer 1

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