March 1, 2019 at 1:09 am
Hello Folks,
After a failover of one of my SQL server instances it happends that one specifed database lost its dbowner.
Dbowner means not the database role.
It means the authorisation for the database which is configured with ALTER AUTHORIZATION ON DATABASE::[xyz] TO [specialUser].
Unfortunatly the apllication which uses this DB is working only with this configuration. So I have to define that user every time after a failover again.
Does anybody knows this problem? And how can it be fixed?
Regards
Alphanew
March 4, 2019 at 1:11 pm
Any chances this is a SQL Native login, not a domain login? Odds are it's SQL Native and the SIDS don't match between the nodes in the AoHA cluster. Every time you fail over there's a mismatch, and you reset the user's SID in the database by doing the ALTER AUTHORIZATION, but it's now wrong on the other side.
Your best option is to drop the login on the Secondary node(s), then use something like sp_help_revlogin to script the login from the current Primary, and recreate it on all the Secondaries using the script. This will retain both the SID and the Password across nodes, and should solve your problem.
Leo
Nothing in life is ever so complex that with a little work it can't be made more complex!
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
March 4, 2019 at 11:20 pm
Hello Leo.
Thanks for helping. The user is not a dmain user. It is a SQL server native user. But to delete this user on the passive side is not possible.
If I try this a notification is poping up and tells me
This user is owner of a database. Please drop that user from this database.
Hmm. It is not possible to drop a user from a passive database. If I shift to the active side, -it means failover- , the user is lost. So we are at the starting point of my problem.
Greetings
Alphanew
March 5, 2019 at 1:36 am
What Leo Miller is referring to is correct. What you really need when you can get some downtime is fail over to the other side, and drop and recreate the login (not the user) with the same SID as on the primary side. See here for more details.
March 5, 2019 at 1:57 am
Hello Beatrix.
Short time after I answering Leo I realized what Leo meant. So I did it in that way you have spoken in your post.
Thanks for helping
Alphanew
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply