1

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?

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

  • Just to add, this works. – Marcus Apr 9 '11 at 15:34
1

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.