AWS RDS Creating users for databases

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?

Solution 1

I didn´t use sp_addlogin to create the login for the user. I did it like this and it worked.

CREATE LOGIN userName WITH PASSWORD = 'y0urPa$$w0rd'

Then I can do

CREATE USER userName FOR LOGIN userName WITH DEFAULT_SCHEMA=dbo

It is important to notice that sp_addlogin will soon be removed in future versions of SQL Server.

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CREATE LOGIN instead.

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addlogin-transact-sql?view=sql-server-2017

Hope this helps

Solution 2

So far I've never tried doing it via SQL, but has done it via SSMS few times without issue. Please try the following (login as the user you specified at AWS RDS console earlier):

1. Create the login. Keep the "server roles" as "public".
2. Under user mapping tab, map the newly created login to the particular database that you desire. Select "db_owner" as the role. I normally just leave the default schema to dbo but that's up to you.

Solution 3

Try this SQL command:

CREATE USER [MyUSER] FOR LOGIN [MyUser] WITH DEFAULT_SCHEMA=[dbo]

Solution 4

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

View more solutions

Comments

[Adil B]

It seems like the WITH DEFAULT_SCHEMA=[dbo] did the trick. Thanks!