SQLServerCentral Article

Fixed Database Roles


In a previous article I discussed how the Public role works and some of the

potential pitfalls. In this article I'd like to continue that discussion by

looking at four of the fixed database

roles; db_datareader, db_datawriter,

db_denydatareader, and db_denydatawriter.

Here is the overview of what each does:

DB_DataReader  Grants selected access to all user tables
DB_DataWriter  Grants insert, update, delete access to all user tables
DB_DenyDataReader  Denies select access to all user tables
DB_DenyDataWriter  Denies insert, update, delete access to all user tables

To give you a feel for how useful they can be, let's revisit a scenario I

posed last time. You have designed an application that will be used by all

employees and they will need select, insert, and delete permissions on all

tables. Here is how you can do it used the fixed roles:

  • Add the Everyone group as a database user
  • Add the Everyone group as a member of DB_DataReader
  • Add the Everyone group as a member of DB_DataWriter

Compared to explicitly granting permissions one table at a time (to Public or

to a role you have created), I think this is both faster and easier. Now let's

continue experimenting by assuming we have implemented permissions as shown

above. You are notified that a contractor will be given domain access (and

therefore will be a member of Everyone) but SHOULD NOT be given any database

access. Here is one way to do it, using fixed roles:

  • Add the Contractor to the database as a user
  • Add the Contractor to the DB_DenyDataReader role.
  • Add the Contractor to the DB_DenyDataWriter role.

Again, I think you can see that using the DB_DenyDataWriter &

DB_DenyDataReader is much easier than creating a new role just for the

contractor, then setting permissions (DENY in this case) on every user table.

Of course, there are a couple things you need to consider. One is that these

roles don't allow very granular permissions. They don't handle situations where

you need to grant or deny access to only a few of the user tables - you're

forced to create additional roles to override the fixed roles. The other is that

not only does membership in one of these roles cover all existing user tables,

it also AUTOMATICALLY applies to any tables that are created later on! Sometimes

that is a good thing, sometimes not. Once you

start creating exceptions, I think it's more effective to create one role per

situation and assign the permissions explicitly.

There are a few stored procedures related to this topic that you might want

to look at:

sp_helpdbfixedroleDisplays a list of the fixed roles
sp_dbfixedrolepermissionDisplays the permissions for each fixed role
sp_addrolememberUsed to add a database user to a role
sp_droprolememberUsed to drop a database user from a role


5 (1)

You rated this post out of 5. Change rating




5 (1)

You rated this post out of 5. Change rating