SQL Permissions: More about the Fixed Roles

February 26, 2001

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_accessadmin sp_addalias
db_accessadmin sp_dropalias
db_accessadmin sp_dropuser
db_accessadmin sp_grantdbaccess
db_accessadmin sp_revokedbaccess

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_securityadmin

DENY

db_securityadmin

GRANT

db_securityadmin

REVOKE

db_securityadmin

sp_addapprole

db_securityadmin

sp_addgroup

db_securityadmin

sp_addrole

db_securityadmin

sp_addrolemember

db_securityadmin

sp_approlepassword

db_securityadmin

sp_changegroup

db_securityadmin

sp_changeobjectowner

db_securityadmin

sp_dropapprole

db_securityadmin

sp_dropgroup

db_securityadmin

sp_droprole

db_securityadmin

sp_droprolemember

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_backupoperator

BACKUP DATABASE

db_backupoperator

BACKUP LOG

db_backupoperator

CHECKPOINT

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_ddladmin

All DDL but GRANT, REVOKE, DENY

db_ddladmin

dbcc cleantable

db_ddladmin

dbcc show_statistics

db_ddladmin

dbcc showcontig

db_ddladmin

REFERENCES permission on any table

db_ddladmin

sp_changeobjectowner

db_ddladmin

sp_fulltext_column

db_ddladmin

sp_fulltext_table

db_ddladmin

sp_recompile

db_ddladmin

sp_rename

db_ddladmin

sp_tableoption

db_ddladmin

TRUNCATE TABLE

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.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers