Export (0) Print
Expand All
6 out of 7 rated this helpful - Rate this topic

Database-Level Roles

To easily manage the permissions in your databases, SQL Server provides several roles which are security principals that group other principals. They are like groups in the Microsoft Windows operating system. Database-level roles are database-wide in their permissions scope. 

There are two types of database-level roles in SQL Server: fixed database roles that are predefined in the database and flexible database roles that you can create.

Fixed database roles are defined at the database level and exist in each database. Members of the db_owner and db_securityadmin database roles can manage fixed database role membership. However, only members of the db_owner database role can add members to the db_owner fixed database role. There are also some special-purpose fixed database roles in the msdb database.

You can add any database account and other SQL Server roles into database-level roles. Each member of a fixed database role can add other logins to that same role.

Important noteImportant

Do not add flexible database roles as members of fixed roles. This could enable unintended privilege escalation.

The following table shows the fixed database-level roles and their capabilities. These roles exist in all databases.

Database-level role name

Description

db_owner

Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.

db_securityadmin

Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.

db_accessadmin

Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.

db_backupoperator

Members of the db_backupoperator fixed database role can back up the database.

db_ddladmin

Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

db_datawriter

Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

db_datareader

Members of the db_datareader fixed database role can read all data from all user tables.

db_denydatawriter

Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.

db_denydatareader

Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

For specific information on database-level fixed role permissions, see Permissions of Fixed Database Roles (Database Engine).

The msdb database contains the special-purpose roles that are shown in the following table.

msdb role name

Description

db_ssisadmin

db_ssisoperator

db_ssisltduser

Members of these database roles can administer and use SSIS. Instances of SQL Server that are upgraded from an earlier version might contain an older version of the role that was named using Data Transformation Services (DTS) instead of SSIS. For more information, see Using Integration Services Roles.

dc_admin

dc_operator

dc_proxy

Members of these database roles can administer and use the data collector. For more information, see Data Collector Security.

PolicyAdministratorRole

Members of the db_ PolicyAdministratorRole database role can perform all configuration and maintenance activities on Policy-Based Management policies and conditions. For more information, see Administering Servers by Using Policy-Based Management.

ServerGroupAdministratorRole

ServerGroupReaderRole

Members of these database roles can administer and use registered server groups. For more information, see Creating Server Groups.

dbm_monitor

Created in the msdb database when the first database is registered in Database Mirroring Monitor. The dbm_monitor role has no members until a system administrator assigns users to the role.

Important noteImportant

Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin. This elevation of privilege can occur because these roles can modify Integration Services packages and Integration Services packages can be executed by SQL Server using the sysadmin security context of SQL Server Agent. To guard against this elevation of privilege when running maintenance plans, data collection sets, and other Integration Services packages, configure SQL Server Agent jobs that run packages to use a proxy account with limited privileges or only add sysadmin members to the db_ssisadmin and dc_admin roles.

The following table explains the commands, views and functions for working with database-level roles.

Feature

Type

Description

sp_helpdbfixedrole (Transact-SQL)

Metadata

Returns a list of the fixed database roles.

sp_dbfixedrolepermission (Transact-SQL)

Metadata

Displays the permissions of a fixed database role.

sp_helprole (Transact-SQL)

Metadata

Returns information about the roles in the current database.

sp_helprolemember (Transact-SQL)

Metadata

Returns information about the members of a role in the current database.

sys.database_role_members (Transact-SQL)

Metadata

Returns one row for each member of each database role.

IS_MEMBER (Transact-SQL)

Metadata

Indicates whether the current user is a member of the specified Microsoft Windows group or Microsoft SQL Server database role.

CREATE ROLE (Transact-SQL)

Command

Creates a new database role in the current database.

ALTER ROLE (Transact-SQL)

Command

Changes the name of a database role.

DROP ROLE (Transact-SQL)

Command

Removes a role from the database.

sp_addrole (Transact-SQL)

Command

Creates a new database role in the current database.

sp_droprole (Transact-SQL)

Command

Removes a database role from the current database.

sp_addrolemember (Transact-SQL)

Command

Adds a database user, database role, Windows login, or Windows group to a database role in the current database.

sp_droprolemember (Transact-SQL)

Command

Removes a security account from a SQL Server role in the current database.

Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.