Neeraj Dwivedi (5/10/2013)
We use database mirroring ( high safety) for our HA needs.Our application uses db_datareader & db_datawriter role for running the applications.
I had set up the application user account on mirror using the SID of primary.
But when ever the fail-over happens, I have to explicitly assign the role to the new primary.(old mirror)
Am i missing something? Is there any way that I should not involve myself when ever the fail-over happens?
Environment :- SQL Server 2008 R2 Standard Edition.
If all you were given is the informatioin above, no access to the systems or anything else, do you think you could answer the question?
Really need more informatiion about the systems, etc. if you want to get a good answer. I set up DB Mirroring and a previous employer, and I had to set it up such that on failover that the SQL users used by the application were synced to that servers logins for those users. This was automated so I didn't have to get involved.
From your description of the problem I'm not sure what you are doing or why at the moment. Unfortunately I also don't have access to a mirrored system to do any exploratory testing.