There are two types of database-level roles:
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 |
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 |
Granted – SELECT
By SSMS
By TSQL
1 2 3 4 5 |
USE [Our_Tech_Ideas] GO ALTER ROLE [db_datareader] ADD MEMBER [login01] GO |
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 |
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 |
Denied – SELECT
By SSMS
By TSQL
1 2 3 4 5 |
USE [Our_Tech_Ideas] GO ALTER ROLE [db_denydatareader] ADD MEMBER [login01] GO |
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 |
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 |
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 |
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
List out the members mapped with the server roles
1 |
sp_helprolemember |
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 […]