Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 22, 2001

SQL Permissions: DataReader & DataWriter Roles

By Andy Warren

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!

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM