Fixing User Login Mapping

You might have been in a situation where you have been asked to sync up the development database with production database. Fair enough. So you started to backup your production database and restore that database in the development environment. You thought to yourself, “That was easy.” But then after an hour has past, one of the developer went to you and said that now he can not access the database that you’ve just restored in the development environment. Doh!

What’s Wrong

For SQL Server authentication logins, SQL Server would assign security identifier (SID). SQL Server would store the login information in the master database. Each database would also store information on who can access the database. They are linked together by the user SID. Since you were only restoring the user database, it is very likely that the SID of the users stored in the master database (the one that grants login to the SQL Server) do not match with the SID of the users stored in the user database that you’ve just restored (the one that grants access to the user database). Thus, we have a situation where we have orphan users.

Detecting Orphan Users

To quickly detect orphan users for a particular user database, you can use the following script (in this example assume that AdventureWorks is the user database):

USE [AdventureWorks]
GO
EXEC sp_change_users_login ‘Report’
GO

Alternatively, you can also use the following script:

USE [AdventureWorks]
GO
SELECT [name], [sid]
FROM sysusers
WHERE name not in (‘guest’, ‘dbo’)
AND sid IS NOT NULL
AND issqluser = 1

Fixing Orphan Users

Now let say that when we run the script above, we see that user ‘Developer1’ is orphan. Assuming that in the development SQL Server, we already have login for ‘Developer1’, to fix this, we can run the following script:

USE [AdventureWorks]
GO
EXEC sp_change_users_login ‘Auto_Fix’, ‘Developer1’
GO

Alternatively, we can also run the following script:

USE [AdventureWorks]
GO
ALTER USER Developer1
  WITH LOGIN = Developer1
GO