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 
'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.

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.

Featured

  • Microsoft Browser Support for TLS 1.0 and 1.1 Ending 2H 2020

    Microsoft announced on Tuesday that its plans to drop support for Transport Layer Security (TLS) protocols 1.0 and 1.1 in its browsers will get delayed by a few months until the second half of this year.

  • Attackers Using Excel Read-Only Files To Obscure Malware

    Attackers can attempt to hide malicious payloads in Excel files sent by e-mail by using a standard Excel feature, according to a Tuesday post by Mimecast researchers.

  • Microsoft 365 Personal and Family Product Unveiled

    Microsoft on Monday announced new "Microsoft 365 Personal and Family subscriptions" to come next month, a new single consumer product providing access to applications such as Excel, PowerPoint and Word.

  • Microsoft Shifting Away from Office 365 Brand Name in April

    Microsoft on Monday announced coming product naming changes, where "Office 365" is mostly getting replaced by the "Microsoft 365" brand.

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.