Using sp_change_users_login to fix SQL Server orphaned users

Using sp_change_users_login to fix SQL Server orphaned users

In this post, I’m going to be looking at sp_change_users_login in order to fix SQL Server orphaned users as a continuation to a previous article. There I looked at a couple of ways to transfer logins from one SQL Server to another and touched upon the issue of the orphaned “security identifier” (SID).
A typical scenario that arises is when the DBA quickly realises that the logins on the SQL Server cannot access the database. They try and add the login to the database as a user and are presented with the error:

Error 15023: User already exists in current database.

The root cause of this problem can be when a login is deleted or the database has been moved or restored to another server but the login transported in the database does not exist on the destination server.
Through SQL Server Management Studio, the database user properties will show the User type: as SQL user without login

sp_change_users_login to the rescue!

I first saw this error a number of years ago and due to my complete lack of experience at the time, one of my first thoughts was that I would have to remove the database users, re-add them all for each login requiring access and then proceed to add the permissions back in for user.
I quickly realised that this would be a massive waste of my time and that there had to a better way and so I proceeded to consult the search engines for a resolution. Unsurprisingly I quickly found many other people who had been in the same situation as me and that sp_change_users_login had been the cure to all their woes.
And here I am writing a post about it Well I never would have imagined that but it was a long time ago and only clever people could put a website together back in those days when blogging platforms were a twinkle in some programmers eye.

How to use sp_change_users_login to fix SQL Server orphaned users

The best thing to do is run the following code against each database you are checking. Run this against each database. It will help you to find all the orphaned logins in your database.

1	USE DatabaseName

2	EXEC sp_change_users_login 'Report';

You will see output like the screenshot attached if there are any sql orphaned users. In this example, user “db_login1” is showing up as an orphaned user.

If you already have a login which you want to map your database user to, you could run the following (note that the first instance of ‘db_login1’ is the user in the database, the second instance is the login to be mapped to) :

1	EXEC sp_change_users_login 'update_one', 'db_login1', 'db_login1';

If you don’t already have a login to map to, you can have sp_change_users_login create one for you and with a password. The following code does this and creates a login with the same name and a password of ‘aaZZww77’ as an example.

1	EXEC sp_change_users_login 'Auto_Fix', 'db_login1', NULL, 'aaZZww77';

UPDATE – 3rd Apr 2018
An alternate way to detect orphaned users in SQL Server or PDW is by running this code for each database:

1	SELECT dp.type_desc, dp.SID, dp.name AS user_name

2	FROM sys.database_principals AS dp

3	LEFT JOIN sys.server_principals AS sp ON dp.SID = sp.SID

4	WHERE sp.SID IS NULL AND authentication_type_desc = 'INSTANCE';

This is an example output:

For identifying orphaned users in Azure SQL Database and SQL Data Warehouse run this code in the master database:

1	SELECT sid FROM sys.sql_logins WHERE type = 'S';

Now run this code in each database:

1	SELECT name, sid, principal_id

2	FROM sys.database_principals

3	WHERE type = 'S'

4	AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')

5	AND authentication_type_desc = 'INSTANCE';

Compare the two lists to see if there are user SID’s in sys.database_principals which are not found in sys.sql_logins

Fix SQL Orphaned Users Using CREATE LOGIN

You can take the SID’s identified in the previous section and use them as part of the CREATE LOGIN statement, example:

1	CREATE LOGIN db_login_1

2	WITH PASSWORD = 'use_a_strong_password_here',

3	SID = 0xB171D3B5A352A846847342C5E46620BA;

If you’re mapping an orphaned user to a login which already exists in master, run this:

1	ALTER USER <user_name> WITH Login = <login_name>;

For more info on how to fix orphaned users, check sp_change_users_login procedure  and this one on troubleshooting orphaned users to view the documentation from Microsoft.