Synchronizing SQL user in Database Mirroring:

 If a login used in an application that involves database mirroring has SQL Login, there can be issues with login failure. While I was working with database mirroring set up between two databases in different server, I ran into an issue where I was not able to connect to the mirror database after the failover. I knew I could use:

sp_change_users_login ‘update_one’, ‘UserName’, ‘UserName’ to synch. This is also a way to resolve orphan user issue after the database is being restored.  The login fails after the database is being restored because the SID (Security Identifier) of that login at the first server will be different from the SID of the second server where the database is restored and same is the case in the database mirroring as well. 

 We cannot update the system catalog in SQL Server 2005/2008 therefore, we cannot use update command in syslogins table. So we have to get the SID from the principal database. In order to get the SID, use the following command:

 User master;

Go

Select SID, name, dbname from syslogins

 Once you get the SID for the particular user then, copy that SID and use in the following query in the mirror database:

 CREATE LOGIN Login Name WITH PASSWORD = ‘Give the Password for this LoginName’, SID = SID from the one that you get from the above query for the user that you want to use for that database.

 This way we can resolve the orphan user issue and we can avoid it using instead of manually applying the sp_change_users_Login script to synch up the user after the database restore or after the failover.

 With mapping SID will best work if the failover is automatic in mirroring session where DBA intervention is not required.

 After you apply the script, you can compare the SID from both instances running the following query to check that user on both instance have the same SID. If it is not the same, the login will fail again. So make sure that you have same SID  in principal database user.

User master;

Go

Select SID, name, dbname from syslogins

 Hope this helps a bit who are running into this issue.

 Thanks,

Abi Chapagai