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

 

Facebook Comments Box
Please like or share this post if you found it helpful, thank you.
Share on Facebook
Facebook
0Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin
Email this to someone
email
Print this page
Print

Comments

  • In case a site is of a superior enough standard, good webmasters normally jump at option. The considerable item necessary to make a smoothie is really a good blender or food processor.

Leave a Reply

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