How to Fix Orphaned Users on an SQL Server

Whenever a database is moved from one SQL Server to another, it’s only the database users which are carried across (so no logins are transferred as part of this). For Windows logins, the fix is simply a matter of re-creating that login on the new server, however SQL logins – which have their own unique identifier – are a little more complex.

There are two strategies which can be used to resolve this. The preferred strategy would be to export that SQL login and import it on the new environment, whereby the SIDs would match up automatically to restore the same level of access as on the original server. This method is the only method which will work if the new environment holds a read-only copy of the database such as a warm standby copy that’s involved in log shipping for instance.

The 2nd strategy involves creating a new SQL login on the new environment which will create a new SID for that login. To match up the new login with the existing DB user, we need to re-associate the two together via a process known as fixing the orphaned users.

Firstly to report on whether there are any orphaned users present for a database, run the following query against the DB;

EXEC sp_change_users_login 'Report'

Then to fix any identified orphaned queries, you can either attempt to fix this automatically via:

EXEC sp_change_users_login 'Auto_Fix', 'user'

… or manually (with more control) via the following query:

sp_change_users_login 'update_one', 'dbUser', 'sqlLogin'

Alternatively

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'