Blocking SQL Server db_datareader, db_datawriter, and db_owner Permissions

By:   |   Comments (6)   |   Related: > Security


I'm in a dilemma. In a database I currently support, the application makes use of db_datareader and db_datawriter to give permissions to the tables. In a different database, it's even worse as the application uses db_owner. We're adding new tables to both databases and we don't want the application to have access to these tables. How can I deal with the use of these three roles? I do not have time to re-architect the security and redo the permissions.  Check out this tip to learn more.


You've hit upon one of the reasons many DBAs dislike the use of all inclusive roles like db_datareader, db_datawriter, and db_owner: if you have to make a security change, it because very problematic to do so. The best option, from an architecture perspective, is to redo the security permissions so that they are the minimum necessary for each user. However, because we're often under time constraints to come up with a solution, it's not possible to make major changes. There is something you can do, however. It involves the use of DENY.

First, let's create a sample database and several users with the appropriate roles so that we can see how the DENY works:

-- Test Database to Show Permissions
-- Use the new DB
USE [TestDB];
-- Create the three users we can work with
-- Add them to the three roles we're concerned about
EXEC sp_addrolemember @rolename = 'db_datareader', @membername = 'Reader';
EXEC sp_addrolemember @rolename = 'db_datawriter', @membername = 'Writer';
EXEC sp_addrolemember @rolename = 'db_owner', @membername = 'DBOwn';
-- Create a simple table to show permissions
CREATE TABLE dbo.SampleTable (SampleColumn INT);

Let's use the EXECUTE AS USER in order to specify test harnesses so we can see how the roles give implicit access:

-- Note, no permissions have been assigned. However, implicit SELECT 
-- permissions are in effect because of the roles
SELECT SampleColumn FROM dbo.SampleTable;
SELECT SampleColumn FROM dbo.SampleTable;
-- Note again, no permissions have been assigned. INSERT, UPDATE,
-- and DELETE are allowed because of implicit permissions due to the roles
INSERT INTO dbo.SampleTable 
(SampleColumn) VALUES (0);
INSERT INTO dbo.SampleTable 
(SampleColumn) VALUES (1);

As we expected, these roles give the users permissions to the table, even though we've not explicitly defined such permissions. This is the way these roles work. Simply being a member of these roles gives the access and that's the problem. Here's where we can see the DENY block access. Let's create a role and assign DENY permissions to the four basic query commands. Then we'll make each user a member of the role.

-- Now let's use DENY to block explicitly
CREATE ROLE [DenyAccess];
EXEC sp_addrolemember @rolename = 'DenyAccess', @membername = 'Reader';
EXEC sp_addrolemember @rolename = 'DenyAccess', @membername = 'Writer';
EXEC sp_addrolemember @rolename = 'DenyAccess', @membername = 'DBOwn';
-- Now go and try the SELECT and INSERT statements again

With the DENY in place, try the test harnesses and you'll see that the users can no longer access the table. The DENY is a trump: it doesn't matter if the user has either implicit or explicit permission, the DENY will block the access. In the case of db_datareader and db_datawriter, there's nothing more that they can do about it. The issue with db_owner is that a user with db_owner can always remove the DENY. However, if you're talking about an application, this type of command is likely not built into the application.

Note: there is one exception and that's if the user is seen as dbo. You can't assign a DENY to dbo and you can't make dbo a member of a role, meaning there is no way to block dbo. You can assign DENY explicitly against the public role, which should apply to dbo. However, it does not and dbo effectively ignores the DENY. Members of the sysadmin server role come into a database as dbo. So does the true database owner (as seen in sys.databases). If an application actually owns the database or the user the application comes in is a member of the sysadmin role, you won't be able to block access in this manner. In the first case you'll have to change out the database owner if you can and then make the user a member of the db_owner role. In the second case, there's nothing you can do short of re-architecting the security from the ground up.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author K. Brian Kelley 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

Comments For This Article

Wednesday, September 18, 2013 - 10:08:25 AM - John Langston Back To Top (26843)

Thank you Brian.

Underscores the need for everyone (end user management and IT) to understand the access requirements of the user community. 

I personally prefer the use of AD groups as a means to apply granualar permissions to objects but even this has a downside if one needs to cross a linked server to get data from another database on another server.

Monday, September 16, 2013 - 9:30:43 AM - Seth Delconte Back To Top (26810)

Thanks Brian.

Tuesday, April 2, 2013 - 1:02:04 PM - AZJim Back To Top (23119)

Personally, I dislike DENY as much as global GRANT permissions.  If you have network acconts, troubleshooting why a person doesn't have permissions can be more difficult.  This happens because SQL Server will take the most restrictive/pessimistic approach to permissions (as it has to).  As an example, if you have permissions from the following network accounts of db_datareader1, db_datareader2, db_datawriter, and if a business user is in all three, should one of the datareaders have a DENY, then the business user will lack permissions in accordance with the DENY.

SQL Server DBAs will eventually have to realize that as SQL Server becomes more strategic in the enterprise (i.e., replacing Unix or mainframe DBMS apps), permissions will have to grow stricter.  Business users will have to identify what tables/views they require access to and the corresponding GRANT permissions will have to be granted at a more granular level.

Tuesday, April 2, 2013 - 7:44:29 AM - Kev Back To Top (23111)

I think the point is that most vendors or developers take a lazy approach and instead of going out of their way to find out what their usres actually need, they put their users into a God group.

But to actually hardcode the necessity for a user to be db_owner or sysadmin is one reason for me at least, why a vendor will fail the selection process.

Wednesday, March 6, 2013 - 11:34:48 AM - Rob Volk Back To Top (22596)

If you're really desperate and have no other alternative, you can try the techniques I've listed here:

It's easy to modify the constraints to use IS_ROLEMEMBER() for database roles.  Unfortunately these won't help for limiting SELECT or DELETE operations.

Wednesday, March 6, 2013 - 9:12:37 AM - Bob St. Aubyn Back To Top (22591)

Brian - you touched on a topic that I have been dealing with for most of my 12+ year career in SQL Server.  In every SQL environment I've worked in, permissions have been assigned with a "db_owner/sysadmin" mentality, mostly because nobody knew how to grant permissions at more granular levels and/or didn't want to take the time.  The, "if I grant dbo (or sysadmin) to the user, I know it will work" approach to granting database permissions, when a company doesn't have the benefit of a DBA on staff, has unfortunately been the norm.  Consequently, in every place I've worked, I've had to pontificate about why this is bad and launch a tedious and frustrating adventure to find out what the users and applications really need and back permission down gradually over time.  And it's so hard to do this after the fact.  The first answer to the question, "what does this application need to be able to do in the database?", will almost certainly be "everything" or "I have no idea".

Then there are the permissions requirements for purchased or consultant-created software packages.  I've seen two different applications that "required" db_owner membership because they were designed to create tables in the database on-the-fly.  Then there was the app that actually had a check to make sure it was a member of the sysadmin role before it would initialize.  I sure hope the hackers never find that one because they'd have have a ball.

Security in SQL Server has been one of the hardest battles I've fought for thus far.  It seems that the "least possible prevelidge" best practice paradigm is known only to DBA's and, unfortunately, either unknown or ignored among other disciplines.

Sorry about the long post, but as you can tell, this has been a major frustration for me for a long time!  Anyway, thanks for the very relevant post!

PS: I wonder how many of your readers realize what they're really granting when they throw a user in the db_owner role!

get free sql tips
agree to terms