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.