<

Mapping Orphan Users in SQL Server

Published on
8,315 Points
2,115 Views
2 Endorsements
Last Modified:
Approved
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)
	DECLARE fixusers CURSOR 
	FOR

	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

	FETCH NEXT FROM fixusers
	INTO @username

	WHILE @@FETCH_STATUS = 0
	BEGIN
	EXEC sp_change_users_login 'update_one', @username, @username
	FETCH NEXT FROM fixusers
	INTO @username
	END

	CLOSE fixusers
	DEALLOCATE fixusers
GO

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
2
Author:TempDBA
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