SQL Permissions: More about the Fixed Roles

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.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles