dbo – Database Owner

In SQL Server, the dbo or Database Owner is a server-level principal that has full access to the owned database. Microsoft’s best practices recommend creating a discrete user, either an Active Directory domain user or group, or a SQL Server Authentication user, to use as the database owner. This post shows how to manage the dbo, or database owner.

Breakfast of a Young Man who is definitely not the dbo, or database owner

Breakfast of a Young Man who is definitely not the dbo, or database owner!        source

We need a Test Database

First, we’ll create a database:

We need a Login, Too!

Next, we’ll create a SQL Server login to serve as the database owner. Note, at this point the database above is owned by the login that created the database – us!

Changing the dbo, or database owner

Here’s the juicy bit, where we change the owner of the owner_demo database to owner_demo_login:

Here’s the documentation for ALTER AUTHORIZATION

Now, let’s check the list of database principals to see who’s got access to it:

╔═══════════════════════╦═══════════════╦══════════════╦════════════════════════════════════╗
║ DatabasePrincipalName ║     Type      ║ principal_id ║                            sid     ║
╠═══════════════════════╬═══════════════╬══════════════╬════════════════════════════════════╣
║ db_accessadmin        ║ DATABASE_ROLE ║        16385 ║ 0x01050000000000090400000000000... ║
║ db_backupoperator     ║ DATABASE_ROLE ║        16389 ║ 0x01050000000000090400000000000... ║
║ db_datareader         ║ DATABASE_ROLE ║        16390 ║ 0x01050000000000090400000000000... ║
║ db_datawriter         ║ DATABASE_ROLE ║        16391 ║ 0x01050000000000090400000000000... ║
║ db_ddladmin           ║ DATABASE_ROLE ║        16387 ║ 0x01050000000000090400000000000... ║
║ db_denydatareader     ║ DATABASE_ROLE ║        16392 ║ 0x01050000000000090400000000000... ║
║ db_denydatawriter     ║ DATABASE_ROLE ║        16393 ║ 0x01050000000000090400000000000... ║
║ db_owner              ║ DATABASE_ROLE ║        16384 ║ 0x01050000000000090400000000000... ║
║ db_securityadmin      ║ DATABASE_ROLE ║        16386 ║ 0x01050000000000090400000000000... ║
║ dbo                   ║ SQL_USER      ║            1 ║ 0x0ABB8D9FCFB2DF4AACA93281390667F3 ║
║ guest                 ║ SQL_USER      ║            2 ║ 0x00                               ║
║ INFORMATION_SCHEMA    ║ SQL_USER      ║            3 ║ NULL                               ║
║ public                ║ DATABASE_ROLE ║            0 ║ 0x01050000000000090400000083741... ║
║ sys                   ║ SQL_USER      ║            4 ║ NULL                               ║
╚═══════════════════════╩═══════════════╩══════════════╩════════════════════════════════════╝

If you look closely above, you’ll see the owner_demo_login is not listed. That’s because it’s listed as dbo, which I’ve highlighted in yellow. We can see the sid column for the dbo user matches up to the sid column in sys.server_principals for the owner_demo_login login with this query:

╔══════════════╦═════════════════════╦═══════════╦══════════════╦════════════════════════════════════╗
║ DatabaseName ║ ServerPrincipalName ║   Type    ║ principal_id ║                sid                 ║
╠══════════════╬═════════════════════╬═══════════╬══════════════╬════════════════════════════════════╣
║ owner_demo   ║ owner_demo_login    ║ SQL_LOGIN ║          280 ║ 0x0ABB8D9FCFB2DF4AACA93281390667F3 ║
╚══════════════╩═════════════════════╩═══════════╩══════════════╩════════════════════════════════════╝

Test It!

We can “login” to the database without having to change our connection properties via the EXECUTE AS statement. That allows us to use the sys.fn_my_permissions dynamic management function to see the permissions the owner has in the database.

The output first shows the results of USER_NAME() and SUSER_SNAME() to prove the permissions are for the owner_demo_login. That looks like:

╔══════════╦══════════════════╗
║ UserName ║ SystemLoginName  ║
╠══════════╬══════════════════╣
║ dbo      ║ owner_demo_login ║
╚══════════╩══════════════════╝

The permissions output looks like:

╔═════════════╦═══════════════════════════════════════════╗
║ entity_name ║              permission_name              ║
╠═════════════╬═══════════════════════════════════════════╣
║ database    ║ CREATE TABLE                              ║
║ database    ║ CREATE VIEW                               ║
║ database    ║ CREATE PROCEDURE                          ║
║ database    ║ CREATE FUNCTION                           ║
║ database    ║ CREATE RULE                               ║
║ database    ║ CREATE DEFAULT                            ║
║ database    ║ BACKUP DATABASE                           ║
║ database    ║ BACKUP LOG                                ║
║ database    ║ CREATE DATABASE                           ║
║ database    ║ CREATE TYPE                               ║
║ database    ║ CREATE ASSEMBLY                           ║
║ database    ║ CREATE XML SCHEMA COLLECTION              ║
║ database    ║ CREATE SCHEMA                             ║
║ database    ║ CREATE SYNONYM                            ║
║ database    ║ CREATE AGGREGATE                          ║
║ database    ║ CREATE ROLE                               ║
║ database    ║ CREATE MESSAGE TYPE                       ║
║ database    ║ CREATE SERVICE                            ║
║ database    ║ CREATE CONTRACT                           ║
║ database    ║ CREATE REMOTE SERVICE BINDING             ║
║ database    ║ CREATE ROUTE                              ║
║ database    ║ CREATE QUEUE                              ║
║ database    ║ CREATE SYMMETRIC KEY                      ║
║ database    ║ CREATE ASYMMETRIC KEY                     ║
║ database    ║ CREATE FULLTEXT CATALOG                   ║
║ database    ║ CREATE CERTIFICATE                        ║
║ database    ║ CREATE DATABASE DDL EVENT NOTIFICATION    ║
║ database    ║ CONNECT                                   ║
║ database    ║ CONNECT REPLICATION                       ║
║ database    ║ CHECKPOINT                                ║
║ database    ║ SUBSCRIBE QUERY NOTIFICATIONS             ║
║ database    ║ AUTHENTICATE                              ║
║ database    ║ SHOWPLAN                                  ║
║ database    ║ ALTER ANY USER                            ║
║ database    ║ ALTER ANY ROLE                            ║
║ database    ║ ALTER ANY APPLICATION ROLE                ║
║ database    ║ ALTER ANY COLUMN ENCRYPTION KEY           ║
║ database    ║ ALTER ANY COLUMN MASTER KEY               ║
║ database    ║ ALTER ANY SCHEMA                          ║
║ database    ║ ALTER ANY ASSEMBLY                        ║
║ database    ║ ALTER ANY DATABASE SCOPED CONFIGURATION   ║
║ database    ║ ALTER ANY DATASPACE                       ║
║ database    ║ ALTER ANY EXTERNAL DATA SOURCE            ║
║ database    ║ ALTER ANY EXTERNAL FILE FORMAT            ║
║ database    ║ ALTER ANY MESSAGE TYPE                    ║
║ database    ║ ALTER ANY CONTRACT                        ║
║ database    ║ ALTER ANY SERVICE                         ║
║ database    ║ ALTER ANY REMOTE SERVICE BINDING          ║
║ database    ║ ALTER ANY ROUTE                           ║
║ database    ║ ALTER ANY FULLTEXT CATALOG                ║
║ database    ║ ALTER ANY SYMMETRIC KEY                   ║
║ database    ║ ALTER ANY ASYMMETRIC KEY                  ║
║ database    ║ ALTER ANY CERTIFICATE                     ║
║ database    ║ ALTER ANY SECURITY POLICY                 ║
║ database    ║ SELECT                                    ║
║ database    ║ INSERT                                    ║
║ database    ║ UPDATE                                    ║
║ database    ║ DELETE                                    ║
║ database    ║ REFERENCES                                ║
║ database    ║ EXECUTE                                   ║
║ database    ║ ALTER ANY DATABASE DDL TRIGGER            ║
║ database    ║ ALTER ANY DATABASE EVENT NOTIFICATION     ║
║ database    ║ ALTER ANY DATABASE AUDIT                  ║
║ database    ║ ALTER ANY DATABASE EVENT SESSION          ║
║ database    ║ KILL DATABASE CONNECTION                  ║
║ database    ║ VIEW ANY COLUMN ENCRYPTION KEY DEFINITION ║
║ database    ║ VIEW ANY COLUMN MASTER KEY DEFINITION     ║
║ database    ║ VIEW DATABASE STATE                       ║
║ database    ║ VIEW DEFINITION                           ║
║ database    ║ TAKE OWNERSHIP                            ║
║ database    ║ ALTER                                     ║
║ database    ║ ALTER ANY MASK                            ║
║ database    ║ UNMASK                                    ║
║ database    ║ EXECUTE ANY EXTERNAL SCRIPT               ║
║ database    ║ CONTROL                                   ║
╚═════════════╩═══════════════════════════════════════════╝

As you can see, owner_demo_login has complete access to the database.

Who owns what?

Use the following simple query to determine which logins own databases:

If any values in the OwnerLogin column are NULL, that indicates the owner login no longer exists.

If you attempt to drop a login that owns a database, you’ll see the following error message:

Msg 15174, Level 16, State 1, Line 16
Login ‘<login_name>’ owns one or more database(s). Change the owner of the database(s) before dropping the login.

Use the ALTER AUTHORIZATION ON DATABASE::<database_name> TO <login_name>; statement to reset the dbo, or database owner, to the desired login.

Summary

We’ve seen how to change database owners, find out who owns databases, and how to see what permissions those database owners have.

Thanks for reading! If you have any questions about this post, please don’t hesitate to post a comment below.

Check out the rest of our series on security!

Ads by Google, Paying the Rent: