2

I manage a SQL Server machine (with SQL Server 2000 and 2005 installed) that was first created on an Active Directory domain that we are transitioning away from and are about to shut down. All of my databases have File Owner's from the old domain.

New accounts have been created that have the correct permissions to run the SQL Server services and execute all necessary SQL Server processes. My question is, what benefit is there to updating the associated File's Owner property on each database?

Do I need to assign a new AD account to each database, or can I leave it as it is since there are already accounts setup that have full admin rights for all of my databases?

Improve this question

1 Answer 1

Reset to default
3

Yes, you need to change the databse owners to valid principals. There are several features that require the database owner principal to be valid, most notably the EXECUTE AS infrasturcture. You may be using these feature whithouth your knowledge, for example an application may be using SqlDependency which requires Service Broker which uses EXECUTE AS. Rather than face long nights of troubleshooting cryptic error messages, do yourself a favour and change all owners on all databases to sa:

alter authorization on database::<dbname> to [sa];

And no, I did not specify sa by mistake.

Improve this answer
2
  • :I copy & pasted your command and changed <dbname> to my db name, and I'm getting an error "Incorrect syntax near the keyword 'authorization'." Is there a mistake in your code?
    – RLH
    Jul 9, 2012 at 16:18
  • 1
    That syntax is for SQL 2005. On SQL 2000 you must use sp_changedbowner
    – Remus Rusanu
    Jul 9, 2012 at 16:32

Your Answer

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

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