The Power of the SQL Server Database Owner

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 

UTHORIZATION ON DATABASE::TestDB TO DatabaseOwn
-- Make the login we created the database owner
ALTER 
Aer;


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 

'InternalUser';
GO 
EXEC sp_addrolemember @rolename = 'db_owner', @membername =

 

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 

();
GO 
SELECT SampleCo
-- See who is coming in
SELECT USER_NAM
Elumn FROM dbo.SampleDatabase;
GO
REVERT;
GO 

ho is coming in
SELECT USER_NAME(
EXECUTE AS USER = 'InternalUser';
GO 
-- See 
w);
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

TO InternalUser;
GO 


-- This works, too, and is th
DENY SELECT ON dbo.SampleDatabase
 e best way to handle
-- permissions: by using a role. Note that we're 

CT ON dbo.SampleDatabase TO DenyRole
-- undoing the DENY against the user

CREATE ROLE DenyRole;
GO 
DENY SEL
E;
REVOKE SELECT ON dbo.SampleDatabase TO InternalUser;
GO 

'InternalUser';
GO


-- These won't work 
DENY SELECT ON dbo.SampleDatabase T


EXEC sp_addrolemember @rolename = 'DenyRole', @membername =
 O dbo;

EXEC sp_addrolemember @rolename = 'DenyRole', @membername = 'dbo';

will work, but note that it doesn't stop dbo


-- Test this with the
EXEC sp_addrolemember @rolename = 'DenyRole', @membername = 'dbo';

-- This   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.

Source Collected from mssqltips.com