Do you know who owns your databases?  Execute sp_helpdb on your SQL Server instances and you might find some surprises under the “owner” column.  It isn’t uncommon to see accounts of people who have left the company or moved on to other roles in the organization that don’t require privileged database access.  Yet these owners still have full database permissions, including the ability to drop the database.  To prevent these security issues and other problems, consider establishing an appropriate database ownership standard for your environments.

Database ownership is an often forgotten detail because it is implicitly set to the database creator’s account.  The owner will initially be a Windows account or SQL login, depending on the authentication method used by the creator.  Note that the owner is always an individual account, not a group or role, so a database created by a sysadmin role member is actually owned by the creator’s individual account instead of a built-in security principal (unless the creator logged in using the “sa” account).

A Best Practice is to change the database owner immediately after creating, restoring or attaching a database.  Unless I have a reason to do otherwise, I specify “sa” as the database owner.  This can be done with sp_changedbowner in SQL 2000 or with ALTER AUTHORIZATION in SQL 2005:

The Significance of the Database Owner

Database ownership is important from a security perspective because the owner account is mapped to the built-in “dbo” user.   The “dbo” user, sysadmin role members and db_owner role members all have full database permissions and can also DROP the database.  The database owner is also used as the authorization of the “dbo” schema, which comes into play with ownership chaining.  With cross-database chaining, the databases involved must have the same owner in order to provide an unbroken chain for “dbo” schema objects.

A difference between the database owner and db_owner role members is that there is exactly one “dbo” user (the database owner) but there may be many users that are db_owner role members.  The owner’s account cannot be explicitly added to the database because the owner is already implicitly mapped to the “dbo” user and an account can be mapped to no more than one user per database.  If you attempt to add the owner as a database user, error message “The proposed new database owner is already a user or aliased in the database” results.

Troubleshooting Database Ownership

The database owner is ultimately identified by the account SID (security identifier).  The creator’s account SID is recorded in 2 places:  1)  at the server level in sys.databases/sysdatabases and  2)  in the database as the dbo user SID in sys.database_principals/sysusers.  These SIDs will normally match but can get out-of-sync following a database restore or attach.  You will also end up with a NULL database owner if the owner’s Windows account is deleted because of the orphaned SID.

Mismatched owner SIDs can result in problems such as

·         Problems executing system stored procedures

·         Problems with tools

·         Broken cross-database ownership chains

The sample queries below will help identify problem database owners.  Problem owners will have mismatched SIDs and/or NULL owner names.  Fortunately, problem database ownership is easy to remedy.  Simply change the database owner using sp_changedbowner or ALTER AUTHORIZATION as you would after creating a new database.  In some cases, you might get an erroneous “The proposed new database owner is already a user or aliased in the database” due to the mismatch.  A workaround in this situation is to temporarily change the database owner to a non-conflicting login and then back to the desired owner.