THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

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.

Published Friday, October 17, 2008 11:32 AM by ejohnson2010

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

sqlagentman said:

There is the option of avoiding orphan users all together: create the login on any SQL instances you would plan on migrating the database between (TEST, PROD, MOCK, DEV, etc.) by using sp_help_revlogin. Check out this KB article from M$: http://support.microsoft.com/kb/918992.

Futher articles on orphan users is available here as well: http://www.sqlservercentral.com/articles/Administration/61648

October 19, 2008 8:52 AM
 

Jason said:

Hey guys I found a simple script to script logins from a user database,it calls a stored procedure to do this. I dont take any credit here is the URL where I found it. http://www.sqlserveroptimizer.com/2011/08/how-to-script-logins-from-user-database-in-sql-server-20052008-r2/

USE MyDatabaseName

DECLARE @login nvarchar(50)

DECLARE logins_cursor CURSOR FOR

SELECT

   l.name

FROM

   sys.database_principals u INNER  JOIN

   sys.server_principals l ON u.sid=l.sid

OPEN logins_cursor

FETCH NEXT FROM logins_cursor INTO @login

WHILE @@FETCH_STATUS = 0

BEGIN

   EXEC sp_help_revlogin  @login

   FETCH NEXT FROM logins_cursor INTO @login

END

CLOSE logins_cursor

DEALLOCATE logins_cursor

GO

December 6, 2011 6:40 AM
 

Jose A. Hernandez said:

Actually, there are 2 well documented ways to move SQL logins from one instance to another and the link from sqlserveroptimizer above is actually not giving the proper credit to the source of the info, which is Microsoft itself.

Here's how to transfer SQL logins between SQL instances running SQL 2000 or from 2000 to 2005 instances:

http://support.microsoft.com/kb/246133

And here is the one where the SQL instances are running 2005 or higher:

http://support.microsoft.com/kb/918992

December 23, 2011 11:29 AM

Leave a Comment

(required) 
(required) 
Submit

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement