A Fix for Database Orphans
A common problem when restoring databases is making orphans of users. Here's a quick fix.
- By Eric Johnson
- 10/22/2008
Because SQL Server manages SIDs for any user logging into SQL Server, you risk "orphaning" users when you do a restore. It's a common problem and here's why.
Typically, restoring a database to the same server from which the backup is taken won't cause an issue, unless you drop the connection and recreate the login. The problem rears its ugly head when you restore a backup to a server that is not at the original location.
So you planned ahead and created the same logins on the new server as they existed on the old server, but why do users end up orphaned?
That's because SQL Server manages the SIDs for SQL Server logins. Thus, there's no guarantee that the new login has the same SID as the original login did. When you restore your database, the users in that database are expecting SIDs that are not there. The next thing you know, you have orphaned users.
One note: Orphans won't occur with Windows Logins, as those SIDs are controlled by Windows or Active Directory. Unless you drop and recreate 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 recreating the user and messing up the permissions in the process? Microsoft provides a handy 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 and it can fix an orphaned user manually or automatically.
Here's an example: I 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 the user, I just 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'
If you want to use the AUTO_FIX action, 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. Using this option has the potential to create logins you don't want, especially if your login names and user names differ.
About the Author
Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology
consulting. He is also the President of the Colorado Springs SQL Server
User Group. He can be contacted at www.consortioservices.com.