SQL Advisor

Fixing SQL Server 2008 Database Orphans

A common problem when restoring databases is making orphans of users. Here's a quick solution.

• By Eric Johnson
• 02/17/2011

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 log-in. The problem rears its ugly head when you restore a backup to a server that's not at the original location.

You planned ahead and created the same log-ins on the new server as they existed on the old server, so why do users end up orphaned?

It's because SQL Server manages the SIDs for SQL Server log-ins. Thus, there's no guarantee that the new log-in has the same SID that the original log-in had. When you restore your database, the users in that database are expecting SIDs that aren't 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, so your user accounts see the SID they're 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 a couple of 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 log-in:

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 log-in name. If no match is found, it will create the appropriate log-in for you. Using this option has the potential to create log-ins you don't want, however, especially if your log-in names and user names differ.