• Adam Thurgar

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


4 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t