July 26, 2011 at 10:39 am
Someone is leaving the company and they own many databases in the environment. If I make a database owner change on the primary instance, will the database owner change on the secondary instance?
Example code run on primary:
ALTER AUTHORIZATION ON DATABASE::[db_name] TO sa;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 26, 2011 at 3:13 pm
No, unfortunately they aren't. The issue being that the database owner detail is stored in the master.sys.sysdatabases table, and not on the user database. There also isn't any way of changing the owner on the destination server, at least not while it's in RECOVERY mode.
The "correct" way to do this is to change the owner on the current live DB, then fail over to the destination DB and change the owner there. In our business we manage SQL servers for multiple companies as we are a "managed services" company. The result is we see a lot of databases not owned by sa and a lot of them are also log shipped. We normally wait till the normal failover testing to change the owners.
It may be possible to change the owner on the destination server if the database is being put into standby mode after each log restore, but I've never tried it.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
July 26, 2011 at 4:19 pm
Thanks for the reply Leo.
This is a DR scenario so the three secondary databases I have are all in Standby/Read-Only mode. The Read-Only part might be the deal-breaker and I'm not sure if I can safely set it to Read-Write, then change the owner, then change it back to Read-Only without adversely affecting Log Shipping. I may try this in a test environment, but it's not mission critical on this pass so I will likely just change the primary DB owner and leave the secondary DB alone for now. A fail-over/change/fail-back is not in the cards for this pass.
In looking into this at the macro level I found many databases with NULL owners as well...probably from logins owning databases that have since been deleted.
The plan is to update the NULL-owned and user-owned databases where possible, and leave a task to update the secondary servers during scheduled DR testing as you suggested. Do you see any issues with that approach or should I not even mess with the primary DB where I can't get the secondary DB to match at the moment?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 26, 2011 at 6:33 pm
I did a quick test, a Standby/Read-Only databse can't have it's database owner changed, so there is definitely something being at least tested against the database.
If you recover the database to change the owner, that will blow the log shipping and you will need to do a new full restore from a current backup. Putting you back in the same position, unless you log on with the sa account and then do the restore.
Having the DR databases not owned by sa isn't normally a problem, and waiting till the next DR test is probably the best option.
Any live DBs not owned by sa should ideally be changed unless there is some application constraint that requires a particlular non-sa owner.
Any database with a NULL owner should be fixed as soon as possible. There are a few management issues that arise when the owner is NULL, particulalry if you try access some features from SSMS.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply