SQL Insider

A Fix for Database Orphans

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

Because SQL Server manages security identifiers (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 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 

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.

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.


  • OneDrive Users To Get Storage Options, Plus New Personal Vault

    Microsoft announced a few OneDrive enhancements, including storage-option additions, plus a new "Personal Vault" feature for added security assurance.

  • Cloud Services Starting To Overtake On-Prem Database Management Systems

    Database management system (DBMS) growth is happening more on the cloud services side than on the traditional "on-premises" side, according to a report by Gartner Inc.

  • How To Replace an Aging Domain Controller

    If the hardware behind your domain controllers has become outdated, here's a step-by-step guide to performing a hardware refresh.

  • Azure Backup for SQL Server 2008 Available at Preview Stage

    Microsoft added the option of using the Azure Backup service to provide recovery support for SQL Server 2008 and SQL Server 2008 R2 when those workloads are hosted on Azure virtual machines.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.