Database ownership

Previously I have posted about the importance of SQL Server Agent jobs being owned by sa and the problems that can be caused by jobs being owned by users. The same applies for the ownership of databases. When you create a database it is owned by the user (either Windows or SQL login) you connected with. Best practice is for databases not to be owned by a user, but owned by a privileged account, usually sa.

The problem with having databases owned by users, usually occurs when that user has been removed from the AD or dropped as a login.

One problem can be if you try to restore a database that had a user owner when it was backed up, but that user no longer exists when doing the restore, the restore can fail.

Another problem can be in removing components that were created when the user owned the database, but when you try to remove those components you are unable to because of this phantom/ghost user. This could apply to removing replication, log shipping or mirroring.

Changing the database owner is easy with the following command.

ALTER AUTHORIZATION ON DATABASE::DatabaseName to sa