Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

The Power of the SQL Server Database Owner

MSSQLTips author K. Brian Kelley By:   |   Read Comments (8)   |   Related Tips: More > 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


Last Update: 3/15/2013


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, September 12, 2013 - 8:56:22 AM - K. Brian Kelley Read The Tip

Bob, you're going to probably want to look at some automation and perhaps an SSIS package. Replication to Jet DBs has been discontinued as of SQL Server 2008R2, if I remember right.


Wednesday, September 11, 2013 - 6:31:49 PM - Bob Jefferson Read The Tip

I would like to copy data from certain SQL 2008 tables into a Microsoft Access file and I would like to automate the process.

If the fields in SQL are updated I would like the fields in Access to be updated automatically.

If it is possible to do can some one provide the details?


Thursday, May 16, 2013 - 10:44:13 AM - Bob St. Aubyn Read The Tip

I've seen this time and time again.  Applications requiring membership in db_owner or even sysadmin because the development team is either ignorant or lazy and doen't want to invest the time to test with lesser permissions.  They know that the app will work with these highly elevated permissions so they don't bother testing with anything else.  In some cases the app code will even check for membership in one of those roles and raise an error if it's user isn't in there.

It's really frustrating to me because I know the security risk this poses and it completely flies in the face of the "least permissions" paradigm.

Compounding the problem is the fact that the same applications often insist on using dynamic, ad-hoc TSQL statements generated by the app at run time or, almost as bad, some ORM that generates garbage TSQL.  At least with the latter the structure of the database isn't exposed, but that's a small consolation when code is executing with carte blanche priviliges.


Friday, April 12, 2013 - 4:45:22 PM - David Bird Read The Tip

I agree an application id should work find as db_owner but I have had vendor applications that would not work without being DBO. DB_Owner just would not work.

Recently I restored a database to a new name that had its application SQL id as the DBO on the database.  I switched the database owner to sa and later discovered the previous DBO sql id still had access on the new database.  It was not listed as a database user. After searching I found it still owned the schema DBO.  Removing it as schema owner removed the permission. This database is using SQL Server 2008 .

Just something to keep in mind.


Monday, March 25, 2013 - 1:33:47 PM - TimothyAWiseman Read The Tip

Nice article.  I would add that generally you want any user (including applications) to have no more access than they actually need.  This can help limit the damage caused if a user gets compromised, such as through a SQL Injection attack.


Thursday, March 21, 2013 - 12:09:50 PM - Jeff Bennett Read The Tip

Thanks for writing this up.  It is kind of a standard for us that the database owner should be 'sa', and every once in a while, you get bizarre results when the database owner MUST be something else.  It is always on a database we inherited and recieved no documentation for, and it is difficult to troubleshoot if you didn't document the change to 'sa'.   Appreciate the investigative work!


Wednesday, March 20, 2013 - 10:12:45 AM - YslGuru Read The Tip

 

We too deal with an enterprise level accounting specialty application (meaning accounting targeted at a specific industry and not general accounting) and the vendor has since day one insisted that the SQL Login the application uses as well as any additional modules/addons it uses is the actual dbo.  For over 5 years I’ve refused to do this opting instead to use a SQL Login/DB User that is a member of the db_owner DB role and so far everything has worked.  Naturally whenever there is any kind of support issue the first thing a support rep says is that we must be using the dbo.  Despite 5 years’ worth of use of the application and hundreds of various support calls/issues with it there has not been one time where the actual dbo had to be in use. 

 

 

 

The real question here is why do so many vendor/apps, even enterprise level apps used by hundreds t thousands of users as our app does, why do they always require the use of the actual database owner, the dbo user and not simply a user who is a member of the db_owner DB role or a more appropriately restricted user/login?  In my opinion this comes from developer ignorance of RDBMS like SQL Server and Oracle and security best practices.  I use to work in support for an enterprise level industry specific software company and I can tell you first hand that developer’s get little to no training in T-SQL/PL-SQL and less then that with security within SQL Server/Oracle other than how to use the sa login and the DB owner. 

 

Who’s fault is it?  I partly blame the decision makers in software development for this because there goals have seldom included proper understanding/use of SQL (regardless of what flavor) and partly blame the developer community which has for the most part pushed hard for a NO-SQL world for years now.  Instead of learning how to properly use SQL these devs spend more time and resources trying to find ways around using SQL instead of just learning how to use it properly.  It really is ridiculous when you think about it.  If we as DBA’s/DB Architects sought to find a way to program without following best practices such as proper naming conventions, scope and in the case of OOP languages, how to code in those without using classes, the traditional developer would throw a fit and cry foul that we were daring to do something as dangerously foolish as this.  Yet this is exactly what they do when it comes to the world of RDBMS/SQL.  


Wednesday, March 20, 2013 - 9:14:29 AM - Uwe Ricken Read The Tip

Hallo Brian,

great article and a pretty fine summary of the permissions for a dbo. I think one really important issue is the fact that a dbo / db_owner is able to increase the data and log files which may have a deep impact to the windows system / storage.

We had such an issue that a "dbo" has expanded the files but didn't recognize that he was working in a shared environment (multi instance server).

Bye, Uwe




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.