in Development, Future Howard, Microsoft

Fix orphaned SQL Server users

I always forget this in between using it, so for the benefit of future Howard, here’s how to fix orphaned users resulting from restoring a SQL database to a different machine.

First, list the orphan users to make sure this is the problem:

EXEC sp_change_users_login 'Report'

Usually I already have the login and password set up on the server, this being the case you can fix the orphan by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If not, and you want/need to create the login and password, you’ll need to do this instead:
EXEC sp_change_users_login ‘Auto_Fix’, ‘user’, ‘login’, ‘password’

Update 5/11/2013: D’oh. This has been wrong for 3 and a half years!
Here’s the correct command for creating the login and password:

EXEC sp_change_users_login 'Auto_Fix', 'user', NULL, 'password'

Check out the docs for more.