1

I have principal server:A and mirror server:B with DB Name: "DBTest" On the server A there is a username (SQL auth.) call: user1 and is mapped to "DBTest". the Same is on Server B, same username and same DB Name (but is in recovery mode due to mirroring). After Fail-over in Principle Server, the Witness switch the DB to the mirror server and everything is fine. except the user: user1 on the server B is not mapped to the "DBTest" on server B. i should every time reassign manually the user1 to the "DBTest". The same i should do when i switch from server B again to server A.

This issue blocks the systems and applications which are connecting with the DB, because the user1 is not mapped automatically to the DB name after fail-over. please help!

Improve this question
3
  • It should be one time only when you failover to secondary for the first time. Mirroring does not move/sync logins. You can create the login with the sane SID as the primary - on the secondary. Check this link out
    – Kin Shah
    Commented Nov 4, 2014 at 14:34
  • thanks for replying. but how to do it manualy ?
    – user3213767
    Commented Nov 4, 2014 at 15:59
  • ALTER USER user_name WITH LOGIN = login_name that will fix the orphan users.
    – Kin Shah
    Commented Nov 4, 2014 at 16:26

1 Answer 1

Reset to default
3

The issue you're running into most likely has to do with your users becoming orphaned on failover. Even though you're using the same logins, the SIDs are different, and don't match up with the users in the database. Basically, you need to recreate the user exactly as it is on server A.

You need to do a CREATE LOGIN with SID = xxx that will create an exact copy of the login on server A. Syntax can be found here. I use sp_help_revlogin to do this; see this knowledgebase article for more information. This will give you a CREATE LOGIN script with the same SID and password to execute on server B.

If you move to SQL Server 2012, you'll have the option of using contained databases. Contained databases can store their own logins, meaning the instance doesn't need to have its own set and preventing orphaned users. These are especially good for databases that get moved around a lot and may have users changing somewhat frequently.

Improve this answer
2
  • Thanks for replying. If Server A now the principle, i need to delete the user completely, then create it using the script which has SID option and also default DB and then map the user to the DB. then fail-over server A to switch to server B, delete the user from server B, then run the same creatUser script, then i think to map it again to the DB Name, then fail-over in server B to switch to server A. is that right as working steps ? if not please tell me the correct sequence
    – user3213767
    Commented Nov 4, 2014 at 15:56
  • You don't have to change anything on A as long as connections are currently working. Delete the instance-level logins on B, then use the script on A to generate the CREATE USER scripts you need. Run those scripts on B and you should be good to go.
    – Neghtasro
    Commented Nov 4, 2014 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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