As sql server dba, you come cross number of request from developer asking about access to execute, select , drop and create permission on database. By creating database role and grant them appropriate permission is good example of  standard practice for sql server on database level. You can make changes at database role, it will applies to all member of that database role. Always good thing to provide minimum permission on database to avoid any mishap.

Database Role in sql server

Default fixed database role exists in all database of sql server

db_datareader -> To grant select to user on user tables

db_datawriter -> To grant insert, update and delete permission on user tables

db_securityadmin ->Using this database role, you modify roles, permission and membership

db_owner -> It acts as own of database, using this database role you can perform all configuration and maintenance changes also drop database permission

db_accessadmin ->  Using db_accessadmin database role, you can add or remove access for Windows logins, groups and SQL Server logins.

db_backupoperator -> It will allow you to take database backup

db_ddladmin -> All DDL permissions are assign to this database role (DROP,CREATE,ALTER and TRUNCATE)

db_denydatareader -> This Database role will prevent you to fire select query on table.

db_denydatawritor ->This database role will prevent you to perform any add, update and delete operation on table

How to create database role in sql server ?

Syntax:

CREATE ROLE db_role_name [Authorization ‘owner_name’]

 [Authorization ‘username’] -> This parameter is optional while creating database role in sql server if don’t provide, it will take default database owner i.e dbo

Example

USE db_name
GO
CREATE ROLE db_executor

Now grant the execute permission to db_executor database role

USE db_name
GO
GRANT EXECUTE TO db_executor

Assign database role to user

sp_addrolemember ‘db_executor’,’John’

To drop database role

DROP ROLE db_executor