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