Mapping Orphan Users in SQL Server

Published on
8,315 Points
2 Endorsements
Last Modified:
We often come with a requirement of moving a database from a server to another. During migration of a database from one server to another, there are various things to be taken care of. One of them is mapping the users to the logins in the destination server. It is often seen that though the users are present in the new database with the same login name as present in the destination server, the access is denied. This happens because of orphan users.

Now, the underlying problem is that the username appears to be correct, but the username is really only a descriptive part of the underlying identity. Adding the database doesnt necessarily map the username to the existing one so a new "SID" (or underlying identity) is created. They look like the same username, but really, underneath it all, they have different SID's.

We need to map the SID of the valid users in the newly migrated data to the valid logins in master database. Once this is done, the users are no longer orphan and the person with the login can access the database with no problem if everything else is up to the place. Generally mapping the login is the top 3 things we should do after restore or attaching a database. Here is a script which will do the mapping
	DECLARE @username varchar(25)

	SELECT UserName = name FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
	and suser_sname(sid) is null and [name] in (select [name] from master.dbo.syslogins) 
	ORDER BY name

	OPEN fixusers

	INTO @username

	EXEC sp_change_users_login 'update_one', @username, @username
	INTO @username

	CLOSE fixusers
	DEALLOCATE fixusers

Open in new window

There are further cases where the users are not mapped. This happens because of unavailability of the login for such users as the login might be present in the server from where we are importing the database. So, we need to drop all such users. Apart from that, even there might be some logins for which users are not created and mapped to the database. If access is required, it has to be handled (manually) by the DBA.

You can read more about trouble shooting orphaned users at :  http://msdn.microsoft.com/en-us/library/ms175475.aspx

And the all important sp_change_users_login at : http://msdn.microsoft.com/en-us/library/ms174378.aspx

References and other recommended reading :-
1. http://www.mssqltips.com/sqlservertip/1590/understanding-and-dealing-with-orphaned-users-in-a-sql-server-database/
2. http://sqlblog.com/blogs/eric_johnson/archive/2008/10/17/fixing-orphaned-users.aspx
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free