Fixing Orphaned Users

  

This is a problem that plagues DBAs everywhere. When you restore a database, you run the risk of orphaning the users in the database. All users are linked via a SID to a login and if you have SQL Server logins, who’s SIDs are managed by SQL Server, you are at risk. Typically a restore to the same server from which the backup was taken won’t cause an issue unless you dropped and recreated the login. Generally the problem rears its ugly head when you restore a backup to a server that was not the original location. You planned ahead and created the same logins on the new server as existed on the old server, so why do the users end up orphaned? As I mentioned earlier, SQL Server manages the SIDs for SQL Server logins so there is no guarantee that the new login has the same SID as the original login did. Then when you restore your database, the users in that database are expecting SIDs that are not there and the next thing you know you have orphaned users. Just a note, this does not occur with Windows Logins because the SID is controlled by Windows or Active Directory. Unless you drop and re-create the user in Windows, the SID of an Active Directory user will be the same on all SQL Servers and hence your user accounts see the SID they are looking for. So, the million dollar question is, how do you fix the problem without dropping and re-creating the user and messing up the permissions in the process? Microsoft provides us with a handy little stored procedure called sp_change_users_login that you can use to fix orphaned users. This procedure can do several things; it can tell you which users are orphaned, it lets you fix an orphaned user manually, and it can attempt to automatically fix your issues. So let’s look at an example. I have deliberately orphaned a user called Annie in the AdventureWorks2008 database. When I run sp_change_users_login with the REPORT option, I can see that I indeed have an orphaned user.
EXEC sp_change_users_login 'REPORT'
…
UserName UserSID
-------- -----------------------------------
Annie 0xA5B5548F3DC81D4693E769631629CE1D
To fix this orphaned user all I have to do is run sp_change_users_login with the UPDATE_ONE action and tell SQL Server the name of my orphaned user and the name of the appropriate login.
EXEC sp_change_users_login 'UPDATE_ONE','Annie','Annie'
There you have it, a simple quick fix to orphaned users that you can use next time you have an issue. I just want to add one more thing regarding the AUTO_FIX action of sp_change_users_login. If you use this option, the procedure tries to automatically fix your orphaned users by matching user name to login name. If no match is found, it will create the appropriate login for you. The only reason I don’t like it is that is has the potential to create logins you don’t want, especially if your login names happen to deliberately differ from your user names