Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Fixed Database 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
Total article views: 10021 | Views in the last 30 days: 0
 
Related Articles
FORUM

CREATE FUNCTION permission denied in database

CREATE FUNCTION permission denied in database

FORUM

User & Their Databases access with Access levels

User,Databases Access,Read Write permissions...List

FORUM

Create Permission in Schema

Create Permission

FORUM

Create table permission denied

Create table permission denied

ARTICLE

Database Permissions

Here is a short How To article on querying for permissions in a SQL Server database

Tags
security    
sql server 7    
strategies    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones