April 30, 2010 at 1:32 am
Hi,
I've got a database in production with 'sa' as an owner and it's logshipped copy in DR, with the owner of the former DBA.
I understand the way it happened was that the database in DR had been restored from a backup under his account, so it is only the master database which has 'former_DBA' account listed as the owner of the user database.
I need to drop that account. I understand I could restore that database under 'sa' login and this will fix the problem in DR.
I've tried running "sp_changedbowner 'sa'" in production, hoping this will replicate into DR, but this did not work.
Are there any other ways to change the owner of the database? The database is in recovery state (not readable).
Thanks.
April 30, 2010 at 6:08 am
Have you tried right-clicking the Logshipped DB in SSM and going to Properties? You should be able to change the owner there.
Then again, I don't have a logshipped DB to test this on. So I'm not sure if it'll work.
April 30, 2010 at 7:06 am
you won't be able to update it until the failover database comes online, so make it part of your failover procedure to correct the dbowner before the app is connected to the failover database.
---------------------------------------------------------------------
May 2, 2010 at 4:41 pm
Brandie Tarvin (4/30/2010)
Have you tried right-clicking the Logshipped DB in SSM and going to Properties? You should be able to change the owner there.Then again, I don't have a logshipped DB to test this on. So I'm not sure if it'll work.
Yes, I now have, but as expected, the option can not be changed until the database comes online. If I bring it online, the log shipping will break, so I will have to re-initialize it.
It appears, that the owner information is stored in both the user database and the master database, the info in the user database has the same owner as in production, but it is not available, because the database is in "Restoring" state. The info in master database has the "old_dba" as the owner, because the database had been restored under his name. However the sp_changedbowner sp would not work until the user database comes online.
I do need to change the owner of the database before it comes online, to be able to drop the "old_dba" login.
It looks like the only option would be to restore the database with norecovery option under sa login.
May 2, 2010 at 4:48 pm
as I said, it cannot be done until database is bought online.:w00t:
---------------------------------------------------------------------
May 3, 2010 at 4:41 am
Norecovery isn't going to help you. It'll leave the db in the same state as it is now. "Restoring".
May 3, 2010 at 7:46 pm
Brandie Tarvin (5/3/2010)
Norecovery isn't going to help you. It'll leave the db in the same state as it is now. "Restoring".
This is the same. If you specify "norecovery" option the database will stay in restoring state. Besides there is no "restoring" option in the restore database command.
May 4, 2010 at 6:09 am
Roust_m (5/3/2010)
Brandie Tarvin (5/3/2010)
Norecovery isn't going to help you. It'll leave the db in the same state as it is now. "Restoring".This is the same. If you specify "norecovery" option the database will stay in restoring state. Besides there is no "restoring" option in the restore database command.
Not sure why you're correcting me. Restoring is a state, not an option and I never said it was an option. EDIT: A Norecovery restore cannot be used to change database owners.
May 4, 2010 at 6:21 am
If you are logged on as 'sa' at the time you do the initial restore of the logshipped database with the norecovery option, the database will be owned by 'sa'.
Once the databases is in the restoring state or read_only/standby (the two options for a logshipped database) you will not be able to change the dbowner until you recover the database (bring it online).
---------------------------------------------------------------------
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply