MSSQL Mirroring Failover: Login failed for user

Question

When MSSQL DB Mirroring fails over, our script won't login, fails on:

Cannot open database requested by the login. The login failed. Login failed for user ...

The way I can fix this is by running:

USE YourDB
GO
EXEC sp_change_users_login 'Auto_Fix', 'MyUsername', NULL, 'MyPassword'
GO

How can I sort this so logins just work automatically?

Answer 1

Found out we need to recreate the logins on the mirror server with the same SID.

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/5b2b7404-69d3-44cf-b522-fae1e6712a8b

Answer 2

Robert Davis is the author of the only DBM book and is a MCM. Try the below if it helps.

http://www.sqlsoldier.com/wp/sqlserver/databasemirroringautomation

Answer 3

Another solution could be to rely on domain accounts, where the SIDs are managed by the domain, rather than sql server accounts, where the default SIDs are instance-specific.