<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Mapping Orphan Users in SQL Server

Published on
8,253 Points
2,053 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
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

Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month