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';
IF NOT EXISTS
WHERE name = @Username)
EXEC sp_addlogin @Username, @Password
EXEC sp_adduser @Username, @Username, 'db_owner'
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?