Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

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

share|improve this answer
    
Just to add, this works. –  Marcus Apr 9 '11 at 15:34
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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