1

The new Senior DBA requested that the previous Senior DBA's Active Directory account be disabled. He forgot to check, however, for databases where the previous DBA was the owner. We now have ~60 databases with an orphaned owner. Is there a way to fix this without temporarily re-enabling the disabled AD Account?

Note: I'm aware of best practices regarding not setting the owner to a user. My previous boss probably was too but neglected to make the change everywhere (60 out of thousands isn't as bad as it could have been).

3
  • Do Alter Authorization or sp_Changedbowner not work if the old owner is disabled? I've never run across that...
    – Kevin3NF
    Feb 3, 2017 at 19:24
  • 1
    A non-sysadmin (diasabled) Login is a good practice for db owner. See here.
    – Kin Shah
    Feb 3, 2017 at 19:38
  • It's my understanding that the usual methods do not work in this scenario. Feb 3, 2017 at 20:29

1 Answer 1

3

Run this for each database.

   -- Change the database owner
    ALTER AUTHORIZATION ON DATABASE::databasename TO sa;
    GO

To generate the script to change them all:

SELECT 'ALTER AUTHORIZATION ON DATABASE::' + db.name + ' TO sa;'
FROM sys.databases db
WHERE owner_sid != 0x01
3
  • 2
    Note that this locks the database as it processes the change, so do this during an off-peak time or during a maintenance window. I learned this the hard way when I locked up a business critical application as it chewed through the ownership change process... until I canceled the operation and left it alone until later.
    – CaM
    Feb 3, 2017 at 19:36
  • This blog has excellent recommendations on who the owner should be. Short answer: create a user "DBOwner" and set the user to disabled and Deny-Connection.
    – CaM
    Feb 3, 2017 at 19:40
  • 4
    Be careful - just db.name might break of db name is weird - has space or some funky character. Always best to use quotename(name).
    – Kin Shah
    Feb 3, 2017 at 19:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.