Fix orphan users in MS SQL Server after restore

When you restore a MS SQL Server to a different machine, you might expect orphan users.
This also happens when you detach a database and then attach it on another server.

You see the users in the Security->Users section of the sql server and also in the database itself, but you can't login with it.

The problem is, that MS SQL stores the SID along the users, and when you restore it on another MS SQL server the link between user names and SID won't match any longer.

Fixing this via GUI is unfortunally not possible, when you try to map the user it trys to create a new sql user and fails, because there already exists such a user.

Fortunally there are a few stored procedures (available since sql 2000 version) which help you fix the logins.

First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

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