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

USE [Our_Tech_Ideas] -- Database name
GO

ALTER ROLE [db_accessadmin] ADD MEMBER [login01] -- Database user name
GO

db_backupoperator

Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT

By SSMQ

By TSQL

USE [Our_Tech_Ideas]
GO

ALTER ROLE [db_backupoperator] ADD MEMBER [login01]
GO

db_datareader

Granted – SELECT

By SSMS

By TSQL

USE [Our_Tech_Ideas]
GO

ALTER ROLE [db_datareader] ADD MEMBER [login01]
GO

db_datawriter

Granted – INSERT, UPDATE and DELETE

By SSMS

By TSQL

USE [Our_Tech_Ideas]
GO

ALTER ROLE [db_datawriter] ADD MEMBER [login01]
GO

db_ddladmin

Granted – Any DDL operation

By SSMS

By TSQL

USE [Our_Tech_Ideas]
GO

ALTER ROLE [db_ddladmin] ADD MEMBER [login01]
GO

db_denydatareader

Denied – SELECT

By SSMS

By TSQL

USE [Our_Tech_Ideas]
GO

ALTER ROLE [db_denydatareader] ADD MEMBER [login01]
GO

db_denydatawriter

Denied – INSERT, UPDATE and DELETE

By SSMS

By TSQL

USE [Our_Tech_Ideas]
GO

ALTER ROLE [db_denydatawriter] ADD MEMBER [login01]
GO

db_owner

Granted with GRANT option: CONTROL

By SSMS

By TSQL

USE [Our_Tech_Ideas]
GO

ALTER ROLE [db_owner] ADD MEMBER [login01]
GO

db_securityadmin

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

By SSMS

By TSQL

USE [Our_Tech_Ideas]
GO

ALTER ROLE [db_securityadmin] ADD MEMBER [login01]
GO

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 :

-- Create a db_executor role
CREATE ROLE db_executor

-- Grant execute rights to the new role
GRANT EXECUTE TO db_executor

Providing db_executor role to a user by SSMS

sp_helprolemember

List out the members mapped with the server roles

Syntax

sp_helprolemember