Fixing Orphaned SQL Logins in AWS RDS

Summary

When you move a SQL Server database to a new server (e.g. by restoring a backup), if that database relies on a server login which is not present on the new server, then you can end up with an orphaned login which means you can't access your database. This can also happen on an existing server if you delete the login, of course.

I encountered the restore-backup-on-new-server version of this problem recently when I was using Amazon's Relational Database Service (Amazon RDS) and it was slightly less straightforward to fix than I anticipated (and the error message was rather misleading as well). Just in case you^[1] ever face the same problem, here is a brief explanation and a script which might help you out and save some time.

Background

In order to connect to a database on an instance of SQL Server using a security principal (database user identity) based on a login, that principal must have a valid login in the master database.

In other words, if you connect to the database using SSMS, you need to be able to find the user under

InstanceName → Security → Logins,

as well as at

InstanceName → Databases → DatabaseName → Security → Users.

But, of course, the user and the login do not need to have the same name. You just need to ensure that a mapping exists between them.

You can see the SQL Server logins on a server instance by running SELECT * FROM sys.server_principals or SELECT * FROM sys.sql_logins.

You can find out if you have orphaned users by running:

EXEC sp_change_users_login 'Report'

Recently a database I was working on was relocated to Amazon's Relational Database Service (AWS RDS). The database in question was a development instance which had been restored from a backup taken from a local installation of Microsoft SQL Server. This was a legacy database which was still using a SQL login so I duly updated the connection string in my app and... it didn't work. I checked the firewall and, well, the title of this article has probably given it away. The database user didn't have a corresponding server login.

Can't I do that Auto Fix thing?

Ordinarily, when faced with an orphaned login for a dev server, it's usual just to run either:

EXEC sp_change_users_login 'Auto_Fix', 'UserName'

or

EXEC sp_change_users_login 'Auto_Fix', 'UserName', null, 'REDACTEDPASSWORD'

With the caveat from Microsoft that the command has been deprecated (is in maintenance mode) and also that you should "Avoid using Auto_Fix in security-sensitive situations." But we're talking about a dev database here.

In any case, when I tried to run:

EXEC sp_change_users_login 'Auto_Fix', 'REDACTEDUSERNAME', null, 'REDACTEDPASSWORD'

it didn't work. Instead, I got this error message:

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

I reckon this is a somewhat misleading message since it wasn't really a permissions error as I was logged in as the database administrator (which I probably shouldn't have been, but it was a dev database).

Solution

Whilst it may be tempting to use sp_addlogin to create the missing login, that command has also been deprecated. The official documentation tells you to use CREATE LOGIN instead.

In the end, I did this:

IF NOT EXISTS (SELECT name FROM sys.sql_logins WHERE name='MissingLoginName')
    BEGIN
        CREATE LOGIN MissingLoginName WITH PASSWORD = 'REDACTED'
    END
ELSE
    BEGIN
        ALTER LOGIN MissingLoginName WITH PASSWORD = 'REDACTED'
    END
ALTER USER MissingLoginName WITH LOGIN = MissingLoginName

This is all very well, but it's slightly annoying as you have the MissingLoginName in five places and some of them have quotes around them and some don't, which makes it slightly tricky to use variables.

This is what I will do next time

Here is some dynamic SQL to make your life easier. If you don't know what the BEGIN TRAN, ROLLBACK and --COMMIT mean then please don't run it without finding out first.

DECLARE @Username SYSNAME
DECLARE @Password SYSNAME
DECLARE @SqlCheckLogin NVARCHAR(MAX)
DECLARE @SqlCreateLogin NVARCHAR(MAX)

SET @Username = 'UserName'
SET @Password = 'Password'

SET @SqlCheckLogin = 'SELECT name FROM sys.sql_logins WHERE name = ' + QUOTENAME(@Username,'''')
SET @SqlCreateLogin = 'IF NOT EXISTS (' + @SqlCheckLogin + ')
    BEGIN
        CREATE LOGIN ' + QUOTENAME(@Username) + ' WITH PASSWORD = ' + QUOTENAME(@Password,'''') + '
    END
ELSE
    BEGIN
        ALTER LOGIN ' + QUOTENAME(@Username) + ' WITH PASSWORD = ' + QUOTENAME(@Password,'''') + '
    END
ALTER USER ' + QUOTENAME(@Username) + ' WITH LOGIN = ' + QUOTENAME(@Username,'''')

BEGIN TRAN
EXEC (@SqlCreateLogin)
EXEC (@SqlCheckLogin)
ROLLBACK
--COMMIT

Conclusion

When I restored a development SQL database to Amazon's RDS and then couldn't login, I began to wonder if I'd made a mistake. In the end it was fairly easy to resolve, but it wasn't quite as straightforward as I was expecting.

But don't use SQL logins, use Windows Integrated Authentication. There are some notes on how to do that on Amazon RDS here.