<

Mapping Orphan Users in SQL Server

Published on
8,204 Points
2,004 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
Comment
Author:TempDBA
2 Comments
LVL 25

Author Comment

by:TempDBA
Sure...
0
LVL 6

Expert Comment

by:yats
Nice article. I was migrating my server and it helped me reduce my work.
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month