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_helpdbfixedrole | Displays a list of the fixed roles |
| sp_dbfixedrolepermission | Displays the permissions for each fixed role |
| sp_addrolemember | Used to add a database user to a role |
| sp_droprolemember | Used to drop a database user from a role |
