What are the Database-Level Roles in SQL Server?

Introduction

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

db_accessadmin

Granted: ALTER ANY USER, CREATE SCHEMA,
Granted with Grant option – Connect

By SSMS

By TSQL

db_backupoperator

Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT

By SSMQ

By TSQL

db_datareader

Granted – SELECT

By SSMS

By TSQL

db_datawriter

Granted – INSERT, UPDATE and DELETE

By SSMS

By TSQL

db_ddladmin

Granted – Any DDL operation

By SSMS

By TSQL

db_denydatareader

Denied – SELECT

By SSMS

By TSQL

db_denydatawriter

Denied – INSERT, UPDATE and DELETE

By SSMS

By TSQL

db_owner

Granted with GRANT option: CONTROL

By SSMS

By TSQL

db_securityadmin

Granted ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION

By SSMS

By TSQL

db_executor role

SQL Server has several fixed database roles such as db_datareader and db_datawriter, which grants the user read and write access respectively to all the tables in the database. Curiously there is no role to grant a user permission to execute stored procedures, but fortunately, this is easily resolved by creating a new role.

The following SQL creates the new role in a database, and then grants it execute rights :

Providing db_executor role to a user by SSMS

sp_helprolemember

List out the members mapped with the server roles

Syntax

 


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *