In previous articles I've discussed how the
roles work, along with some potential pitfalls. In this article I'll discuss the
remaining fixed roles.
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:
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
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
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.
All DDL but GRANT, REVOKE, DENY
REFERENCES permission on any table
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.
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.