Database Fixed Roles 1

All versions of SQL Server use role-based security, which allows you to assign permissions to a role, or group of users, instead of to individual users. Fixed server and fixed database roles have a fixed set of permissions assigned to them. Fixed server roles have a fixed set of permissions and server-wide scope. They are intended for use in administering SQL Server and the permissions assigned to them cannot be changed. Logins can be assigned to fixed server roles without having a user account in a database. Fixed database roles have a pre-defined set of permissions that are designed to allow you to easily manage groups of permissions. Members of the db_owner role can perform all configuration and maintenance activities on the database.

Fixed database roles

db_owner

A member of the db_owner role can do anything inside the database. Now there is a difference between a member of the db_owner role and the dbo user. That difference is that if someone maps into the database as the dbo user, that person bypasses all security checks. An example of this is anyone who is a member of the sysadmin fixed server role. They map in as dbo. And as a result, they don't receive security checks.

The db_owner role should be given out only when necessary. Some applications will require it, which is a headache, but rarely will actual people need it (unless the application is connecting using their credentials). Like sysadmin, which is returned as a member of every fixed server role if you use the IS_SRVROLEMEMBER() function, For instance, a user who is a member of db_owner but who is not a member of db_securityadmin will still return a 1 if you execute the following query:

SELECT IS_MEMBER('db_securityadmin');  

You would want to add a user to the db_owner role if you have a user who needs to make extensive modifications to all aspects of a database:

• Creating users
• Adding them to roles
• Creating tables/views and stored procedures,
• Adding security settings for tables, views, and stored procedures

Things to remember:

• The db_owner role allows a user to do anything within the database.
• DBAs who are already members of the sysadmin fixed server role come in as dbo and don't need this role explicitly granted to them.
• Normal users should not be a member of this role.
• Applications might require their user account to be a member of this role.

db_securityadmin

Like the securityadmin fixed server role, the db_securityadmin fixed database role manages security. In this case, it manages role membership (with the exception of db_owner) as well as permissions on securables. As a result, it's another role you want to keep a close eye on. Generally a lot of folks won’t use this role. Typically the DBAs manage security within the database and they're already coming in as dbo. There may be some rare instances where it would be used. Therefore, if you see any members of this role within a database, it's worth checking out. You might want to use db_securityadmin when you need to grant privileges to a trusted user and allow them to manage privileges across an application.

• The db_securityadmin role can manage role membership and permissions on securables.
• Again, since DBAs usually manage security and are usually coming in as dbo, this role is little used.
• Normal users should not be a member of this role.
• Applications should tend not to need this role.
• Since it's little used, you should audit its membership for exceptions.

db_accessadmin

The db_accessadmin role also manages security, but handles access to the database, as the name implies. The db_accessadmin role grants, denies, or revokes permission to enter the database for logins. Combined with db_securityadmin, and you can completely manage security into and throughout the database. Like db_securityadmin, though, access into the database is usually handled by DBAs. If they aren't members of the sysadmin fixed server role, they are members of the securityadmin fixed server role. As a result, this role should also be rarely used.

• The db_accessadmin role can allow access into or block access to the database for logins.
• Again, since DBAs usually manage security and have an appropriate server-level role, this role is little used.
• Normal users should not be a member of this role.
• Applications should tend not to need this role.
• This is another role you should audit for membership exceptions.

db_backupoperator

The db_backupoperator allows a member of the role to take backups of the database. However, it's only going to allow native backups, as in the standard backups through SQL Server itself. If you're using a third party product, chances are it is usually the methods which allow for high speed backups. Unfortunately, these methods require the login executing them to be a member of the sysadmin fixed server role. As a result, this role tends to be of limited usefulness. Add to it that you're backing up to a local drive, and it's rare to see a non-DBA having this level of access, even in a development system. Because of all these things, this is another role that is typically not used much.

• The db_backupoperator role allows a user to take backups of the database.
• Most 3rd party backup utilities utilize methods that require sysadmin rights, which this doesn't give.
• Another role that is little used because this functionality is usually handled by DBAs or a service account.
• Normal users should not be a member of this role.
• Applications should tend not to need this role, though I have seen exceptions.