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|
Got a question or a comment? Email me!