Database Owner Troubles

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:

SQL 2000:

EXEC MyDatabase..sp_changedbowner ‘sa’;

SQL 2005 and SQL 2008

ALTER AUTHORIZATION ON DATABASE::MyDatabase to sa;

 

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.

 

SQL 2000:

IF OBJECT_ID(N'tempdb..#owners', 'U') IS NOT NULL

      DROP TABLE #owners;

 

CREATE TABLE #owners

(

      database_name sysname NOT NULL,

      sys_databases_sid varbinary(85) NOT NULL,

      sys_databases_owner nvarchar(256) NULL,

      sys_users_sid varbinary(85) NULL,

      sys_users_owner nvarchar(256) NULL

);

 

INSERT INTO #owners

      (

            database_name,

            sys_databases_sid,

            sys_databases_owner

      )

      SELECT

            name,

            sid,

            SUSER_SNAME(sid)

      FROM master.dbo.sysdatabases;

 

EXEC sp_MSforeachdb '

      UPDATE #owners

      SET sys_users_sid = (

                  SELECT sid

                  FROM [?].dbo.sysusers

                  WHERE name = ''dbo''),

            sys_users_owner = (

                  SELECT SUSER_SNAME(sid)

                  FROM [?].dbo.sysusers

                  WHERE name = ''dbo'')

      WHERE database_name = ''?''

      ';

 

SELECT * FROM #owners

WHERE sys_databases_sid <> sys_users_sid;

 

IF OBJECT_ID(N'tempdb..#owners', 'U') IS NOT NULL

      DROP TABLE #owners;

GO

SQL 2005:

IF OBJECT_ID(N'tempdb..#owners', 'U') IS NOT NULL

      DROP TABLE #owners;

 

CREATE TABLE #owners

(

      database_name sysname NOT NULL,

      sys_databases_sid varbinary(85) NOT NULL,

      sys_databases_owner nvarchar(256) NULL,

      sys_users_sid varbinary(85) NULL,

      sys_users_owner nvarchar(256) NULL

);

 

INSERT INTO #owners

      (

            database_name,

            sys_databases_sid,

            sys_databases_owner

      )

      SELECT

            name,

            owner_sid,

            SUSER_SNAME(owner_sid)

      FROM sys.databases;

 

EXEC sp_MSforeachdb '

      UPDATE #owners

      SET sys_users_sid = (

                  SELECT sid

                  FROM [?].sys.database_principals

                  WHERE name = ''dbo''),

            sys_users_owner = (

                  SELECT SUSER_SNAME(sid)

                  FROM [?].sys.database_principals

                  WHERE name = ''dbo'')

      WHERE database_name = ''?''

      ';

 

SELECT * FROM #owners

WHERE

      sys_databases_sid <> sys_users_sid

      OR sys_databases_owner IS NULL;

 

IF OBJECT_ID(N'tempdb..#owners', 'U') IS NOT NULL

      DROP TABLE #owners;

GO

 

posted @ Sunday, January 13, 2008 12:34 PM

Print

Comments on this entry:

# re: Database Owner Troubles

Left by JohnFx at 1/16/2008 9:58 AM
Gravatar
Good article. Database ownership in SQL seems like it is often more complicated than it really needs to be and can get really confusing.

# re: Database Owner Troubles

Left by Dan Guzman at 1/17/2008 7:10 AM
Gravatar
I agree that database ownership and login/user mapping is unnecessarily complicated. I blame Microsoft SQL Server's Sybase roots along with the need to maintain backwards compatibility.

# re: Database Owner Troubles

Left by Bob Martin at 2/28/2008 8:41 AM
Gravatar
This helps answer another question I had posted in the newsgroups about EXECUTE AS failing when the database was created in a machine account vs my account. For whatever reason, databases created under the machine account mapped dbo to sa, where as databases created in other accounts were mapped to the login. For now, I know I need to add to my scripts changing dbo to sa when I create databases.

# re: Database Owner Troubles

Left by Peter at 9/2/2008 4:44 AM
Gravatar
Very good article!

# re: Database Owner Troubles

Left by Reddy Kiran at 9/18/2008 11:13 AM
Gravatar
Nice Helpful Article

# re: Database Owner Troubles

Left by croware at 11/11/2008 11:25 AM
Gravatar
you're great man! good article

# re: Database Owner Troubles

Left by Idol at 12/11/2008 8:32 PM
Gravatar
Really good, thank you.

# re: Database Owner Troubles

Left by rocky at 3/17/2009 12:44 AM
Gravatar
instead of 'sa' can we have 'NT AUTHORITY\SYSTEM' as owner of the database. If not can you explain why? the reason i am asking this because we are facing the similar scenario as stated above(owner leaving the team), last week unfortunately one of my team member removed the admin group and we dont have sa password, then we have to drop that database and restore it from the backup luckily we have that one.

# re: Database Owner Troubles

Left by Dan Guzman at 3/17/2009 9:46 PM
Gravatar
Yes, you can instead use a built-in Windows principal if you prefer. Be aware of the security implications, though. Processes running under that Windows principal will have database owner permissions in that database.

# re: Database Owner Troubles

Left by Mike Robens at 3/29/2009 4:37 PM
Gravatar
Thankyou, you have explained the problem perfectly and the solution works a treat.

# re: Database Owner Troubles

Left by Chandrasekhar kannali at 6/14/2009 7:06 AM
Gravatar
Thank you very much.It is very good and usefull article.

# re: Database Owner Troubles

Left by alphatross at 10/1/2009 7:23 AM
Gravatar
An alternative to using 'sa' as the 'dbo' for all Databases is to create an Active Directory "service account" style user named after the Database and make that dbo. The advantage is that it only has access to one database, and extra info such as a contact for the database can be stored in the AD User object. Also, Linked Servers can alias to this Windows account to access a database (there are distributed query performance advantages to this, but obvious security drawbacks). The Windows account can be set to have zero Windows permissions and even interactive Login rights revoked.

# re: Database Owner Troubles

Left by Innocent Gumbo at 1/4/2010 4:49 AM
Gravatar
Dan, I am impressed by this. Yes I am especially on explaining the Role(db_owner) and the user( db owner). Just at the rignt time for me. We usually get confused by the 2. You just stated that the role can have as many users mapped to it and the other only applies once in a Database!! This is cool. Thanks

# re: Database Owner Troubles

Left by vibhas at 6/25/2010 2:12 AM
Gravatar
Thanks for sharing vital info regarding databases.

# re: Database Owner Troubles

Left by steve at 9/26/2010 9:41 AM
Gravatar
I just checked mine and nearly swore with what I found! Guess I found out just in time!

# re: Database Owner Troubles

Left by JBL at 10/28/2010 2:01 AM
Gravatar
Excellent article. Can anybody comment on the following. We have an application that utilises SQL.
We have found one of our customers using the data base directly to run report writing functions.
Does anybody have any idea on where we stand legally

# re: Database Owner Troubles

Left by Gangu at 12/1/2010 2:22 AM
Gravatar
Who is the Owner of 'sa' login?

dbo or sysadmin

# re: Database Owner Troubles

Left by guzmanda at 2/20/2011 3:54 PM
Gravatar
I'm not sure I understand your question. Logins do not have owners. All members of the sysadmin server role, including the 'sa' login, become the dbo user in all databases even if the login does not actually own the database.

# re: Database Owner Troubles

Left by Jakub Berezanski at 2/23/2011 2:14 AM
Gravatar
A small improvement to the sample queries: surround the ? in the FROM clauses with square brackets to account for special characters in database names (e.g. "Sample-DB-Name"):

FROM [?].sys.database_principals

# re: Database Owner Troubles

Left by guzmanda at 2/23/2011 7:02 AM
Gravatar
Good point about enclosing the database name. The best practice in object naming is to conform to identifier naming rules so that one doesn't need to enclose database names. But robust code should work regardless so I'll change the scripts.

Thanks for the suggestion.

# re: Database Owner Troubles

Left by gregrae at 5/29/2012 6:10 PM
Gravatar
You're awesomge Dan Guzman! Thanks for solving my issue.
Comments have been closed on this topic.