dbo – Database Owner

2019-05-18 · by Hannah Vernon · in basics, security

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! source

We need a Test Database

First, we’ll create a database:

IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = N'owner_demo')
BEGIN
    ALTER DATABASE owner_demo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE owner_demo; 
END
CREATE DATABASE owner_demo;

IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = N'owner_demo')

BEGIN

ALTER DATABASE owner_demo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE owner_demo;

END

CREATE DATABASE owner_demo;

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!

IF EXISTS (SELECT 1
    FROM sys.server_principals sp
    WHERE sp.name = N'owner_demo_login'
        AND sp.type_desc = 'SQL_LOGIN'
    )
BEGIN
    DROP LOGIN owner_demo_login;
END
CREATE LOGIN owner_demo_login 
WITH PASSWORD = N'terrible_password'
    , CHECK_POLICY = OFF
    , CHECK_EXPIRATION = OFF;
GO

IF EXISTS (SELECT 1

FROM sys.server_principals sp

WHERE sp.name = N'owner_demo_login'

AND sp.type_desc = 'SQL_LOGIN'

)

BEGIN

DROP LOGIN owner_demo_login;

END

CREATE LOGIN owner_demo_login

WITH PASSWORD = N'terrible_password'

, CHECK_POLICY = OFF

, CHECK_EXPIRATION = OFF;

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:

ALTER AUTHORIZATION on DATABASE::owner_demo TO owner_demo_login;

1	ALTER AUTHORIZATION on DATABASE::owner_demo 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:

USE owner_demo;
SELECT DatabasePrincipalName = dp.name
    , [Type] = dp.type_desc
    , dp.principal_id
    , dp.sid
FROM sys.database_principals dp
ORDER BY dp.name;

USE owner_demo;

SELECT DatabasePrincipalName = dp.name

, [Type] = dp.type_desc

, dp.principal_id

, dp.sid

FROM sys.database_principals dp

ORDER BY dp.name;

╔═══════════════════════╦═══════════════╦══════════════╦════════════════════════════════════╗
║ 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:

SELECT DatabaseName = d.name
    , ServerPrincipalName = sp.name
    , [Type] = sp.type_desc
    , sp.principal_id
    , sp.sid
FROM sys.databases d
    INNER JOIN sys.server_principals sp ON d.owner_sid = sp.sid
ORDER BY sp.name;

SELECT DatabaseName = d.name

, ServerPrincipalName = sp.name

, [Type] = sp.type_desc

, sp.principal_id

, sp.sid

FROM sys.databases d

INNER JOIN sys.server_principals sp ON d.owner_sid = sp.sid

ORDER BY sp.name;

╔══════════════╦═════════════════════╦═══════════╦══════════════╦════════════════════════════════════╗
║ 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.

USE owner_demo;
EXECUTE AS USER = N'dbo';
SELECT UserName = USER_NAME()
    , SystemLoginName = SUSER_SNAME();
SELECT mp.entity_name
    , mp.permission_name
FROM sys.fn_my_permissions(NULL, N'DATABASE') mp
REVERT

USE owner_demo;

EXECUTE AS USER = N'dbo';

SELECT UserName = USER_NAME()

, SystemLoginName = SUSER_SNAME();

SELECT mp.entity_name

, mp.permission_name

FROM sys.fn_my_permissions(NULL, N'DATABASE') mp

REVERT

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:

SELECT DatabaseName = d.name
    , OwnerLogin = sp.name
FROM sys.databases d
    LEFT JOIN sys.server_principals sp ON sp.sid = d.owner_sid
ORDER BY d.name;

SELECT DatabaseName = d.name

, OwnerLogin = sp.name

FROM sys.databases d

LEFT JOIN sys.server_principals sp ON sp.sid = d.owner_sid

ORDER BY d.name;

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!

3 Responses

Damir Čaklović

2022-02-28 at 10:13 PM · Reply →

Great, but it is a mistake in Test it like “USE ownder_demo;” ! It shall be “USE owner_demo;” –> not ownder but owner!

Loading...
1. Hannah Vernon
  
  2022-02-28 at 10:56 PM · Reply →
  
  nice catch Damir – it’s fixed now. Thanks!
  
  Loading...
Sat'k

2022-03-23 at 1:58 AM · Reply →

really a great info

Loading...

dbo – Database Owner

We need a Test Database

We need a Login, Too!

Changing the dbo, or database owner

Test It!

Who owns what?

Summary

Related

3 Responses

Leave a ReplyCancel reply

Search

Categories

Pages

Meta