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.

USE DatabaseName
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) :

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.

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:

SELECT dp.type_desc, dp.SID, dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.SID = sp.SID
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:

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

Now run this code in each database:

SELECT name, sid, principal_id
FROM sys.database_principals
WHERE type = 'S'
AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')
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:

CREATE LOGIN db_login_1
WITH PASSWORD = 'use_a_strong_password_here',
SID = 0xB171D3B5A352A846847342C5E46620BA;

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

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

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

With any server migration, ideally you want things to run smoothly and re-creating SQL Server logins and passwords from scratch is not something you really want to or should be doing.

I am currently involved in a complete server replacement for one of our SQL Server clusters. In this environment, mixed authentication is enabled.

Back in the day when I was learning about transferring databases from one server, I quickly discovered that restoring a database from one server to another was not as straightforward as I had anticipated. I came across a hurdle which I needed to overcome in the form of the SID’s.

So first I’ll talk about the ways in which you can transfer logins from one SQL Server to another and explain about the SID’s further down the post.

Ways to transfer SQL logins and passwords between instances of SQL Server

Now you can do this the easy way or the hard way! 🙂

You can manually set up copies of your logins on your new server with passwords (if you know them) or you can automate this task with the help of either a T-SQL script or SSIS.

I don’t know about you but I don’t like manual and I would recommend that you automate this each time.

Using T-SQL to transfer logins and passwords between SQL Server instances

You can obtain a script from Microsoft here. You run the script, it creates a stored procedure called sp_help_revlogin on your SQL Server. When you run the stored procedure, it outputs CREATE LOGIN statements for all your server logins including their passwords and “sids”.

You can then take that script, run it on your new SQL Server to set up the server logins there. Obviously taking care to look over it before you execute it to make sure that it has captured everything and to ensure that you are not copying over anything that you do not need on your new instance.

The script will set up the logins on your new server in using the default language configured for that server so make sure that is configured correctly first.

Using the transfer logins task in SSIS to copy logins and passwords between SQL Server instances

There is a transfer logins task within SSIS which will also do the job. In the screenshot attached, I am using the new version of SSIS which ships with SQL Server 2012. The transfer logins task falls under “other tasks” in the the SSIS toolbox and is available in older versions of SSIS.

So hopefully you can see from the screenshot that the transfer logins task is fairly self explanatory. The great thing about SSIS is that it holds your hand in many ways and makes things as easy as possible.

You can create connections from this task, enter the login details for your source and destination, choose which logins to copy and you can configure the task to behave in different ways when the login exists.

As you can see I have circled “CopySids” so now is a good time to write about them in more detail.

So what is a SID?

SID is short for “security identifier” A SID is an internal id which gets assigned to a server login when the login is created.

The SID can be viewed by querying the sys.server_principals system view or you can also view it by looking at sys.syslogins

SELECT principal_id, sid, name
FROM sys.server_principals

SELECT sid, name
FROM sys.syslogins

Each server login, be that a SQL login, Windows login or one which was created using a certificate or asymmetric key, gets assigned a SID. So lets imagine an example scenario where a new login is created and it happens to be a SQL Server login, that login gets named “server_login1”.

You then create a database login inside the AdventureWorks2012 database using the server login just created. Lets call that “db_login1”.

Now view the SID’s in sys.server_principals and sys.database_principals from AdventureWorks2012 and the SID will be of the same value.

SELECT sid
FROM sys.server_principals
WHERE name = 'server_login1'

USE AdventureWorks2012
GO
SELECT sid
FROM sys.database_principals
WHERE name = 'db_login1'

Unless you create your logins on your new server using a method such as the sp_help_revlogin or the SSIS transfer logins task with “CopySids” enabled, your logins will be created with new SIDs.

I am not sure whether it is possible that your new server will ever assign the same SID but I do know that it is very unlikely. Perhaps you have more chance of winning the lottery jackpot 🙂

So what problems does this cause?

As the SIDs are different, when you come to copy across/restore your databases from your old server, the database logins simply won’t work as they will be orphaned from their original SID.

You may encounter this error for example if you try and add a server login to a database which you have just copied across.

Error 15023: User already exists in current database

This will then need to be corrected.

There is a procedure that you can run in order to do this and it basically takes the server login and database login and marries them up by name with the same SID.

This procedure is called sp_change_users_login and I’m going to talk about that in this post.