Mapping Orphan Users in SQL Server

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

Select all 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