Fixing SQL Server Login SID by mapping it to Database User

When you restore a database from another server, especially for setting Availability Group, Database Mirroring, and Log Shipping, the client application may not able to use database and getting login failed for user after failover. It's due to a well known problem called "SID mapping" between the SQL Server Login and the Database User. If you recreate a SQL Server login (ie: not a Windows one), by default you get a new security ID (SID), even though you have the same user name and password. But the SID in the Database User is brought from the original (primary) database, which only linked to the SID of the corresponding SQL Server Login in the original (primary) server.

In order to fix this problem permanently, you should create the SQL Server Logins in secondary server by explicitly specifying the SID, e.g.

1. In primary database, get the SID of the database user:
USE LoginTest;
GO
SELECT sid FROM sysusers WHERE name = 'GregTest';

2. In the secondary server, after you restored the database into it, you can take a look in the user mapping and see that the Database User is NOT linked to any Login:
USE LoginTest
GO
EXEC sp_change_users_login 'Report';

3. You should fix it by creating a new SQL Server Login in the secondary server with the same SID:
CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd', SID = 0x2261C43EFD53F240AA989A8FB9E084DC

4. Check again the SID mapping, you should see no missing anymore: