Database-Level Roles - SQL Server (2023)

  • Article
  • 8 minutes to read

Applies to: Database-Level Roles - SQL Server (1) SQL Server Database-Level Roles - SQL Server (2) Azure SQL Database Database-Level Roles - SQL Server (3) Azure SQL Managed Instance Database-Level Roles - SQL Server (4) Azure Synapse Analytics Database-Level Roles - SQL Server (5) Analytics Platform System (PDW)

To easily manage the permissions in your databases, SQL Server provides several *roles that 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.

To add and remove users to a database role, use the ADD MEMBER and DROP MEMBER options of the ALTER ROLE statement. Analytics Platform System (PDW) and Azure Synapse doesn't support this use of ALTER ROLE. Use the older sp_addrolemember and sp_droprolemember procedures instead.

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

(Video) Server roles , Database roles & Application roles in SQL Server || Ms SQL

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

You can add any database account and other SQL Server roles into database-level roles.


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

The permissions of user-defined database roles can be customized by using the GRANT, DENY, and REVOKE statements. For more information, see Permissions (Database Engine).

(Video) 049 Understanding SQL Server Roles

For a list of all the permissions, see the Database Engine Permissions poster. Server-level permissions cannot be granted to database roles. Logins and other server-level principals (such as server roles) cannot be added to database roles. For server-level security in SQL Server, use server roles instead. Server-level permissions cannot be granted through roles in SQL Database and Azure Synapse.

Fixed-database roles

The following table shows the fixed-database roles and their capabilities. These roles exist in all databases. Except for the public database role, the permissions assigned to the fixed-database roles cannot be changed.

Fixed-Database role nameDescription
db_ownerMembers of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL Server. (In SQL Database and Azure Synapse, some maintenance activities require server-level permissions and cannot be performed by db_owners.)
db_securityadminMembers of the db_securityadmin fixed database role can modify role membership for custom roles only and manage permissions. Members of this role can potentially elevate their privileges and their actions should be monitored.
db_accessadminMembers 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_backupoperatorMembers of the db_backupoperator fixed database role can back up the database.
db_ddladminMembers of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database. Members of this role can potentially elevate their privileges by manipulating code that may get executed under high privileges and their actions should be monitored.
db_datawriterMembers of the db_datawriter fixed database role can add, delete, or change data in all user tables. In most use cases this role will be combined with db_datareader membership to allow reading the data that is to be modified.
db_datareaderMembers of the db_datareader fixed database role can read all data from all user tables and views. User objects can exist in any schema except sys and INFORMATION_SCHEMA.
db_denydatawriterMembers of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareaderMembers of the db_denydatareader fixed database role cannot read any data from the user tables and views within a database.

The permissions assigned to the fixed-database roles cannot be changed. The following figure shows the permissions assigned to the fixed-database roles:

Database-Level Roles - SQL Server (6)

Special roles for SQL Database and Azure Synapse

These database roles exist only in the virtual master database. Their permissions are restricted to actions performed in master. Only database users in master can be added to these roles. Logins cannot be added to these roles, but users can be created based on logins and then those users can be added to the roles. Contained database users in master can also be added to these roles. However, contained database users added to the dbmanager role in master cannot be used to create new databases.

Role nameDescription
dbmanagerCan create and delete databases. A member of the dbmanager role that creates a database, becomes the owner of that database, which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database. Members of the dbmanager role don't necessarily have permission to access databases that they don't own.
db_exporterApplies only to Azure Synapse Analytics dedicated SQL pools (formerly SQL DW).
Members of the db_exporter fixed database role can perform all data export activities. Permissions granted via this role are CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL DATA SOURCE, ALTER ANY EXTERNAL FILE FORMAT.
loginmanagerCan create and delete logins in the virtual master database.


The server-level principal and the Azure Active Directory Administrator (if configured) have all permissions in the SQL Database and Azure Synapse without needing to be members of any roles. For more information, see SQL Database Authentication and Authorization: Granting Access.

(Video) Databases: SQL Server database level roles for creating tables (2 Solutions!!)

Some database roles aren't applicable to Azure SQL or Azure Synapse:

  • db_backupoperator is not applicable in Azure SQL database (not managed instance) and Azure Synapse serverless pool because backup and restore T-SQL commands are not available.
  • db_datawriter and db_denydatawriter are not applicable to Azure Synapse serverless because it just reads external data.

msdb roles

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

msdb role nameDescription



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 Integration Services Roles (SSIS Service).



Members of these database roles can administer and use the data collector. For more information, see Data Collection.
PolicyAdministratorRoleMembers of the db_ PolicyAdministratorRole database role can perform all configuration and maintenance activities on Policy-Based Management policies and conditions. For more information, see Administer Servers by Using Policy-Based Management.


Members of these database roles can administer and use registered server groups.
dbm_monitorCreated 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.


(Video) Understanding SQL Server Database Roles - Fixed & User Defined

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.

Working with database-level roles

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

sp_helpdbfixedrole (Transact-SQL)MetadataReturns a list of the fixed database roles.
sp_dbfixedrolepermission (Transact-SQL)MetadataDisplays the permissions of a fixed database role.
sp_helprole (Transact-SQL)MetadataReturns information about the roles in the current database.
sp_helprolemember (Transact-SQL)MetadataReturns information about the members of a role in the current database.
sys.database_role_members (Transact-SQL)MetadataReturns one row for each member of each database role.
IS_MEMBER (Transact-SQL)MetadataIndicates whether the current user is a member of the specified Microsoft Windows group or Microsoft SQL Server database role.
CREATE ROLE (Transact-SQL)CommandCreates a new database role in the current database.
ALTER ROLE (Transact-SQL)CommandChanges the name or membership of a database role.
DROP ROLE (Transact-SQL)CommandRemoves a role from the database.
sp_addrole (Transact-SQL)CommandCreates a new database role in the current database.
sp_droprole (Transact-SQL)CommandRemoves a database role from the current database.
sp_addrolemember (Transact-SQL)CommandAdds a database user, database role, Windows login, or Windows group to a database role in the current database. All platforms except Analytics Platform System (PDW) and Azure Synapse should use ALTER ROLE instead.
sp_droprolemember (Transact-SQL)CommandRemoves a security account from a SQL Server role in the current database. All platforms except Analytics Platform System (PDW) and Azure Synapse should use ALTER ROLE instead.
GRANTPermissionsAdds permission to a role.
DENYPermissionsDenies a permission to a role.
REVOKEPermissionsRemoves previously granted or denied permissions.

Public database role

Every database user belongs to the public database role. When a user hasn't been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Database users cannot be removed from the public role.


The examples in this section show how to work with database-level roles.

A. Adding a User to a database-level role

The following example adds the User 'Ben' to the fixed database-level role db_datareader.

ALTER ROLE db_datareaderADD MEMBER Ben; GO

B. Listing all database-principals that are members of a database-level role

The following statement returns all members of any database role.

(Video) Roles in Database | How Roles Created in SQL | Why Roles used?

SELECT roles.principal_id AS RolePrincipalID , AS RolePrincipalName , database_role_members.member_principal_id AS MemberPrincipalID , AS MemberPrincipalNameFROM sys.database_role_members AS database_role_members JOIN sys.database_principals AS roles ON database_role_members.role_principal_id = roles.principal_id JOIN sys.database_principals AS members ON database_role_members.member_principal_id = members.principal_id; GO

Related content

  • Security Catalog Views (Transact-SQL)
  • Security Stored Procedures (Transact-SQL)
  • Security Functions (Transact-SQL)
  • Securing SQL Server
  • sp_helprotect (Transact-SQL)
  • Server roles in Azure SQL Database


What are database level roles in SQL Server? ›

There are two types of database-level roles: fixed-database roles that are predefined in the database and user-defined 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 database role can manage fixed-database role membership.

What are the database access levels in SQL? ›

Database access levels are, from lowest to highest, Connect, Resource, and DBA. Use the corresponding keyword to grant a level of access privilege. You can modify the database schema if you own the database object that you intend to modify.

What are the differences between server level permissions and database level permission? ›

Permissions are the types of access granted to specific securables. At the server level, permissions are assigned to SQL Server logins and server roles. At the database level, they are assigned to database users and database roles.

What are DB roles? ›

A database role specifies a set of database level permissions that let a user perform specific tasks. Database roles are not fixed and can be created, modified, and removed. Database roles let you collect users into a single unit to which you can apply permissions.

What is difference between application role and database role? ›

You can use application roles to enable access to specific data to only those users who connect through a particular application. Unlike database roles, application roles contain no members and are inactive by default. Application roles are enabled by using sp_setapprole, which requires a password.

How many types of roles are there in SQL Server? ›

SQL Server provides three types of roles you can use to restrict access to data in your database: server-level roles, database-level roles, and application-level roles.

What are the 5 types of database users? ›

System Analysts. Application Programmers / Back-End Developers. Naive Users / Parametric Users. Sophisticated Users.

How do I grant database level permissions in SQL Server? ›

Grant table-level permissions in SQL Server
  1. Enter a descriptive Login name, select SQL Server authentication, and enter a secure password. ...
  2. Select the User Mapping tab, check the box next to the desired database, confirm that only 'public' is selected, and click OK.

What are the different types of database permissions? ›

See EM - Introduction. BBj provides two types of database permissions: standard permissions, and object level permissions. Standard Permissions provide the least amount of control over access to databases and are provided for backward compatibility purposes and to make the assignment of permissions very easy to manage.

What is the difference between roles and permissions? ›

Roles provide a way for community administrators to group permissions and assign them to users or user groups. Permissions define the actions that a user can perform in a community. When they assign roles, community administrators consider the tasks of a user in the context of a particular community.

Which is the highest database role? ›

db_owner. The users in the db_owner fixed database roles provide the highest permissions in a database. Users have the privilege to create, drop, alter, write, or drop the database.

What is database role vs schema? ›

Schemas and Roles in SQL Server terminology are both collections. Schemas are a collection of objects (tables, views, stored procedures and so forth.) Roles are a collection of rights: server roles for rights at the server/instance level and database roles, which are collections of rights within a given database.

What are the different roles in database environment? ›

There are four distinct types of people that participate in the DBMS environment: data and database administrators, database designers, application developers, and the end-users.

How do you assign a role to a database? ›

  1. Creating and Assigning a Role – First, the (Database Administrator)DBA must create the role. ...
  2. Syntax – CREATE ROLE manager; Role created. ...
  3. Grant privileges to a role – ...
  4. Grant a role to users. ...
  5. Revoke privilege from a Role : REVOKE create table FROM manager;
  6. Drop a Role : DROP ROLE manager;
  7. Explanation –
Sep 27, 2018

What are the four 4 forms of roles? ›

Four-role typology:

Expert prescriber. Communication facilitator. Problem-Solving Process Facilitator.

What are different types of roles? ›

5 types of acting roles
  • Background role. A background role is a part that typically involves moving in the background of a scene and interacting with other background actors. ...
  • Cameo. ...
  • Recurring character. ...
  • Side character. ...
  • Series regular.
Mar 11, 2022

What are the different types of user roles? ›

There are three types of User Roles:
  • Administrator: creates new users, has access to Admin panel.
  • Manager: can create and edit checks and reports.
  • Report Viewer: can view checks and reports.
Feb 3, 2022

What are the 3 main types of databases? ›

hierarchical database systems. network database systems. object-oriented database systems.

What are the different levels of Access? ›

The two most common access levels are public and private, which denote, respectively; permission across the entire program scope, or permission only within the corresponding class. A third, protected, extends permissions to all subclasses of the corresponding class.

What are the 7 categories of access controls? ›

The seven main categories of access control are directive, deterrent, compensating, detective, corrective, and recovery.

What are the 5 areas of access control? ›

The 5 Different Types of Access Control
  • Manual access control.
  • Mechanical access control.
  • Electronic access systems.
  • Mechatronic access control.
  • Physical access systems.

What are the 3 control systems? ›

Three basic types of control systems are available to executives: (1) output control, (2) behavioral control, and (3) clan control. Different organizations emphasize different types of control, but most organizations use a mix of all three types.

Who are the 3 database users? ›

Classification of Users
  • End Users.
  • Application Programmers.
  • Database Administrator.
Jul 3, 2021

How many categories of database users are there? ›

Database users are categorized based up on their interaction with the database. These are seven types of database users in DBMS.

What are different types of database users and their roles? ›

According to the tasks performed by the database users on the databases, we can categorize them into seven categories as follows:
  • Database Administrators (DBA)
  • Database Designers.
  • System Analysts.
  • Application Programmers / Back-End Developers.
  • Naive Users / Parametric Users.
  • Sophisticated Users.
Dec 12, 2022

What are the levels of database? ›

The three levels present in this architecture are Physical level, Conceptual level and External level.

What are database roles and groups? ›

Groups and roles can simplify control of database access. Groups are used to apply permissions to a list of users, while roles are used to associate subject privileges and permissions with an application. A group is an identifier that can be used to apply permissions to a list of users associated with the identifier.

What are the 4 types of users? ›

Four basic types of Users. (and how to identify them)
  • Socializers:
  • Achievers:
  • Explorers:
  • Players:
Feb 23, 2021

What are the three categories of database users? ›

Chapter 14 Database Users
  • Application user. The application user is someone who accesses an existing application program to perform daily tasks.
  • Sophisticated user. Sophisticated users are those who have their own way of accessing the database. ...
  • Application Programmers. ...
  • Database Administrators (DBA)

What are the database level principals? ›

Database Principal Types

As with server principals, there are three main types of database principals: users, groups and certificate based principals. Users are used to grant database level permissions to a linked login. They can be based on a SQL Login or on a Windows Login.

What are the 4 types of database management system? ›

Types of DBMS
  • Relational database.
  • Object oriented database.
  • Hierarchical database.
  • Network database.
Jul 3, 2021

What are the 4 types of relationships in a database? ›

There are three types of relationships between the data you are likely to encounter at this stage in the design: one-to-one, one-to-many, and many-to-many. To be able to identify these relationships, you need to examine the data and have an understanding of what business rules apply to the data and tables.

What are the 5 roles in a group? ›

Honey's Five Team Roles
  • LEADER: makes sure team has clear objectives and members are engaged. ...
  • CHALLENGER: questions effectiveness and drives for results. ...
  • DOER: encourages progress and takes on practical jobs. ...
  • THINKER: produces ideas and thinks through those proposed by others. ...
  • SUPPORTER: eases tension and promotes harmony.

What are the 6 roles in a group? ›

Group roles have an important function in workplace teams because they help members work together effectively.
Here are 10 common group roles organizations assign to team members in the workplace:
  • Facilitator. ...
  • Initiator. ...
  • Arbitrator. ...
  • Notetaker. ...
  • Coach. ...
  • Coordinator. ...
  • Evaluator. ...
  • Compromiser.

What are the 4 Team Roles? ›

Here are four roles for a team: Leader, Facilitator, Coach or Member. All these are the components of a team, but remember that these need not be exclusive. A leader can act as a facilitator and a coach as well at different times.


1. Assign database Role in Sql Server
(Sql Is Easy)
2. SQL Security for Everybody: Understanding Logins, Users, and Roles - Josh Smith
3. SQL Server 2016 Part 4 - Databases, Logins, Users, Roles and Schemas
4. 051 SQL Server Roles Demonstration
(SQL Server)
5. Quick Tutorial - Users and Permissions in SQL Server
(Keil Jones)
6. SQL Server DBA Tutorial 58- How to Create Database Role in SQL Server
Top Articles
Latest Posts
Article information

Author: Merrill Bechtelar CPA

Last Updated: 01/03/2023

Views: 5446

Rating: 5 / 5 (50 voted)

Reviews: 81% of readers found this page helpful

Author information

Name: Merrill Bechtelar CPA

Birthday: 1996-05-19

Address: Apt. 114 873 White Lodge, Libbyfurt, CA 93006

Phone: +5983010455207

Job: Legacy Representative

Hobby: Blacksmithing, Urban exploration, Sudoku, Slacklining, Creative writing, Community, Letterboxing

Introduction: My name is Merrill Bechtelar CPA, I am a clean, agreeable, glorious, magnificent, witty, enchanting, comfortable person who loves writing and wants to share my knowledge and understanding with you.