July 7, 2011 at 7:44 am
How would I go about changing owners of a database that are within a mirror?
Jonathan
July 7, 2011 at 7:51 am
Would I have to remove mirroring then change the owner then setup mirroring back?
July 8, 2011 at 5:55 am
exec sp_changedbowner at the principal
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 8, 2011 at 7:32 am
I tried this at the owner but did not have any luck.
So I just ended up removing the mirroring, changing the owner then going back and setting backup the mirroring.
Jonathan
July 8, 2011 at 7:43 am
what was the command you used to do this
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 8, 2011 at 7:51 am
exec sp_changedbowner 'sa'
July 8, 2011 at 7:54 am
Jonathan Marshall (7/8/2011)
exec sp_changedbowner 'sa'
as you're not using a USE statement, what database focus did you have set for the query window?
also did the user account exist on the mirror server, you need to ensure accounts are synchronised between the 2 instances?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 8, 2011 at 7:55 am
Error message
Msg 954
The database x cannot be opened because it is acting as a mirror database
July 9, 2011 at 4:01 am
Jonathan Marshall (7/8/2011)
Error messageMsg 954
The database x cannot be opened because it is acting as a mirror database
i am able to change the owner at the principal with no issues, however this does not filter through to the mirror. failover manually and change the owner is how i did it eventually
That error leads me to believe you ran the command against the mirror database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 9, 2011 at 6:27 am
My apologies if I was not clear but I meant on the mirrored databases I could not change the ownership.
Jonathan
July 13, 2011 at 1:53 am
Always know that there are some commands which will run on the principal database and not the mirror.So remove mirroring change database owner and reconfigure mirroring.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
October 20, 2011 at 2:10 am
You cant change anything on the mirrored databases.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
October 20, 2011 at 2:11 am
Remove mirroring and reset again,that is how i do it.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
December 6, 2011 at 8:31 am
You could fail over to partner, change owner at mirror, fail back to principal.
Assuming you'd be allowed to do that given the disruption during fail over!
April 16, 2013 at 8:42 am
If we need to do modifications on mirror database, we must make it writable (set partner off + restore with recovery). But if the mirroring should be reset, we must restore the last backup of the principal database, then the log, then the mirroring, which means all the modifications we did on mirror database is meaningless...
Assume that the dbowner of the principal database is "A", and your SQL account is "B". when you restore the backup of the principal database to the DR instance, the dbowner of the mirror database will be "B". How could we change "B" to "A" on the mirror database if we do not want to do the failover, please?
regards
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply