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! source
We need a Test Database
First, we’ll create a database:
|
1 2 3 4 5 6 |
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!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 |
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:
|
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:
|
1 2 3 4 5 6 7 |
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:
|
1 2 3 4 5 6 7 8 |
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.
|
1 2 3 4 5 6 7 8 |
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:
|
1 2 3 4 5 |
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!
Great, but it is a mistake in Test it like “USE ownder_demo;” ! It shall be “USE owner_demo;” –> not ownder but owner!
nice catch Damir – it’s fixed now. Thanks!
really a great info