The Power of the SQL Server Database Owner

By: K. Brian Kelley   |   Updated: 2013-03-15   |   Comments (10)   |   Related: > Security

Problem

I have a couple of databases supporting applications. The first application requires that it's user be a member of the db_owner role. The second application actually has to own the database. What are the effects of having such permissions?  Check out this tip to learn more.

Solution

As you might guess, being a member of db_owner or owning the database means you can pretty much do anything within the database. Even if there aren't explicit permissions given, a member of the db_owner role or a user that maps in as dbo (the database owner) can still query a table, execute a stored procedure, create or modify an object, and even modify security in the database. Let's see what each scenario results in security wise. First, let's create a sample login and a sample database to test with:

-- Create a login that will own the database that we create
CREATE LOGIN DatabaseOwner WITH PASSWORD = 'Some19Difficult80Password!';
GO 
-- Create the database
CREATE DATABASE TestDB;
GO 
-- Make the login we created the database owner
ALTER AUTHORIZATION ON DATABASE::TestDB TO DatabaseOwner;
GO 

Let's also create a user within the database that's a member of the db_owner role. There's a small, but important, difference between being a member of the db_owner role and actually mapping in as dbo.

-- Use the database and create another user that's a member of the db_owner role
USE TestDB;
GO 
CREATE USER InternalUser WITHOUT LOGIN;
GO 
EXEC sp_addrolemember @rolename = 'db_owner', @membername = 'InternalUser';
GO 

Let's also create a sample table to query against. Note that we aren't granting any explicit permissions. Therefore, by nature of being either the database owner or a member of the db_owner role, the security principals can access the table.

-- Create a sample table to show access
CREATE TABLE dbo.SampleDatabase (SampleColumn INT);
GO 

Here are the test harnesses to demonstrate the capability of each security scenario:

-- Note there are no permissions given 
EXECUTE AS LOGIN = 'DatabaseOwner';
GO 
-- See who is coming in
SELECT USER_NAME();
GO 
SELECT SampleColumn FROM dbo.SampleDatabase;
GO
REVERT;
GO 
EXECUTE AS USER = 'InternalUser';
GO 
-- See who is coming in
SELECT USER_NAME();
GO 
SELECT SampleColumn FROM dbo.SampleDatabase;
GO
REVERT;
GO 

Now that we've show that the database owners have access in both scenarios, let's see what we can do to block access:

-- Try to use DENY on the database owner
-- This works, but we try to avoid explicitly
-- putting a permission on a user
DENY SELECT ON dbo.SampleDatabase TO InternalUser;
GO 
-- This works, too, and is the best way to handle
-- permissions: by using a role. Note that we're 
-- undoing the DENY against the user
CREATE ROLE DenyRole;
GO 
DENY SELECT ON dbo.SampleDatabase TO DenyRole;
REVOKE SELECT ON dbo.SampleDatabase TO InternalUser;
GO 
EXEC sp_addrolemember @rolename = 'DenyRole', @membername = 'InternalUser';
GO
-- These won't work 
DENY SELECT ON dbo.SampleDatabase TO dbo;
EXEC sp_addrolemember @rolename = 'DenyRole', @membername = 'dbo';
EXEC sp_addrolemember @rolename = 'DenyRole', @membername = 'dbo';
-- This will work, but note that it doesn't stop dbo
-- Test this with the test harness for LOGIN = 'DatabaseOwner'
DENY SELECT ON dbo.SampleDatabase TO PUBLIC;

Note that in the case of dbo, you can't explicitly assign a permission or a role. You can try a workaround by assigning a permission directly against the public role, but though intuitively you would think it works, it doesn't. The dbo user effectively ignores the DENY issued to the public role. This is the main issue if an application must own the database (or creates the database so it is the owner by default). It can do anything within the database and there is no effective block short of re-architecting security.

Next Steps

• Understand the fixed database roles available in SQL Server.
• Learn how the owner can effect databases using cross-database ownership chaining.
• Make note of the various ways a user can enter a database, including as the owner.
• Understand the fixed server roles available in SQL Server.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips