SQL Server – Updating DBO Accounts After Active Directory Shutdown

permissionssql serversql-server-2000sql-server-2005

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?

Best Answer

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.

Related Question