In previous articles I've discussed how the

Public role and the DataReader/DataWriter

roles work, along with some potential pitfalls. In this article I'll discuss the

remaining fixed roles.

DB_AccessAdmin

This role let's you delegate the ability to add or remove users from a specific

database. In my testing I found that a login added to this role could not

execute sp_adduser, but sp_dropuser would work fine. Both are outdated, the

preferred method is to use sp_grantdbaccess and sp_revokedbaccess which work

fine. This user is restricted to using only existing logins, they cannot create

new ones. Using sp__dbfixedrolepermission validates my testing, here is the list

of permissions it returned for this role:

DB_SecurityAdmin

This role offers the ability to manage permissions, ownership, and roles -

but only with logins that have already been granted access to the database. If

you're using SQL logins to manage permissions this could certainly be useful. I

prefer to assign NT groups to SQL roles and then manage security by

adding/removing members from the NT groups. Here is the list from

sp_dbfixedrolepermission:

DB_BackupOperator

BOL says that users that are a member of this role can do backups, plus DBCC

and Checkpoint. Checkpoint works, DBCC does not - at least the ones I tried! If

you look at the output from sp_dbfixedpermission below, it does not list DBCC as

a permission granted to the role. I'm not sure how often this role will be

useful, since most backups are done as scheduled jobs. Also, there is no

corresponding DB_RestoreOperator role - a chicken and the egg situation, since

you if you are restoring a database you can't see who is a member of what role

until AFTER you restore it! Restores must be done by a member of SysAdmins or

DBCreator roles.

DB_DDLAdmin

This role allows it's members to issue DDL commands only. This is useful if

you have developers that are making schema changes or where you want to let

users have their own objects. Keep in mind that since the object they created

will be owned by them and not by DBO, you will have to deal with ownership

chains unless you use sp_changeobjectowner prior to putting the objects into

production. Books online indicates that members of this role cannot execute

Grant, Deny, or Revoke, but in my testing I found the members of role CAN manage

permissions on objects they own.

DB_Owner

Has all permissions in the database. One thing to remember is that objects

created by members of this role will belong to the user and not to DBO (andy.testtable

rather than dbo.testtable) - unless the user is also a member of sysadmins. If

ownership chains are something you want to avoid, execute sp_changeobjectowner 'object','dbo'

while connected as a member of sysadmins.

Wrap Up

As with Public and the DataReader/DataWriter roles, these roles offer some

potential savings in administration time, but they also have some shortcomings

that can trip you up. Use them when it makes sense. Got a question or a comment?

Email me! If you have time, rate this article and let me know how I'm doing.