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
|
1 2 3 4 5 |
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
|
1 2 3 4 5 |
USE [Our_Tech_Ideas] GO ALTER ROLE [db_backupoperator] ADD MEMBER [login01] GO |
db_datareader
Granted – SELECT
By SSMS
By TSQL
|
1 2 3 4 5 |
USE [Our_Tech_Ideas] GO ALTER ROLE [db_datareader] ADD MEMBER [login01] GO |
db_datawriter
Granted – INSERT, UPDATE and DELETE
By SSMS
By TSQL
|
1 2 3 4 5 |
USE [Our_Tech_Ideas] GO ALTER ROLE [db_datawriter] ADD MEMBER [login01] GO |
db_ddladmin
Granted – Any DDL operation
By SSMS
By TSQL
|
1 2 3 4 5 |
USE [Our_Tech_Ideas] GO ALTER ROLE [db_ddladmin] ADD MEMBER [login01] GO |
db_denydatareader
Denied – SELECT
By SSMS
By TSQL
|
1 2 3 4 5 |
USE [Our_Tech_Ideas] GO ALTER ROLE [db_denydatareader] ADD MEMBER [login01] GO |
db_denydatawriter
Denied – INSERT, UPDATE and DELETE
By SSMS
By TSQL
|
1 2 3 4 5 |
USE [Our_Tech_Ideas] GO ALTER ROLE [db_denydatawriter] ADD MEMBER [login01] GO |
db_owner
Granted with GRANT option: CONTROL
By SSMS
By TSQL
|
1 2 3 4 5 |
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
|
1 2 3 4 5 |
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 :
|
1 2 3 4 5 |
-- 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
|
1 |
sp_helprolemember |
Comments
I remember i got three kings
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.
[…] Administrator, you may need to identify the permissions that have been granted to a user-created database role. To begin, use the query provided by Microsoft to list all of the database roles that are currently […]