By David Postlethwaite

At my presentation on SQL Server Management Studio at SQL Saturday in Exeter I promised to write some articles on Gethyn’s blog about the bits I didn’t manage to cover.

A scenario
A DBA creates a new database and by doing so he become the owner of that database.
He then leaves the company and his Windows account is deleted.
When you try to view the database properties in SQL Server Management Studio you will receive the error “Property Owner is not available”

When SSMS tries to open the database it check the permissions of the owner and because it can no longer find it in Active Directory it aborts the request

You will not be able to view the properties until a new owner is assigned.
This is quite simple just issue this command on the database

sp_changedbowner 'sa' 

You cannot just create a new Windows user with the same name. The underlying SIDs will not match.

If the owner of the database is a SQL user then it’s not possible to delete that user but SQL Server can’t manage Active Directory so is unaware when an account has been deleted.

Also note that if the owner of an SQL Agent job is deleted that Agent job will not run because, once again, SQL checks the permissions of the owner and can’t find it in AD so will not continue.

To avoid this scenario it is best practice to ensure that all databases and agent jobs are owned by “sa” unless there is a good reason not to.