Database Mirroring – Login failure after role switch

After configuring database mirroring and testing a failover, you may be unable to log into the new principal database. This occurs because the database user is not associated with a SQL server login (mismatched SIDs). Running sp_change_users_login ‘auto_fix’, ‘userName’ will correct this, but we need to avoid the situation altogether so a seamless failover is possible.

During the mirroring setup, we can run the following query on the principal to generate the SQL necessary to correctly create the logins on the mirror server.

SELECT
'create login [' + p.name + '] ' +
case when p.type in('U','G') then 'from windows ' else '' end +
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
FROM sys.server_principals p
LEFT JOIN sys.sql_logins l
ON p.principal_id = l.principal_id
LEFT JOIN sys.credentials c
ON l.credential_id = c.credential_id
WHERE p.type in('S','U','G')
AND p.name != 'sa'