Database Level Roles

Database-level roles are database-wide in their permissions scope.

There are two types of database-level roles:

Fixed-database roles that are predefined in the database.

User-defined database roles that you can create.

Database level role
Database Role

Fixed-Database Roles

The following table shows the fixed-database roles and their capabilities.

Fixed-Database role nameDescription
db_ownerMembers of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL Server. (In SQL Database and Azure Synapse, some maintenance activities require server-level permissions and cannot be performed by db_owners.)
db_securityadminMembers of the db_securityadmin fixed database role can modify role membership for custom roles only and manage permissions. Members of this role can potentially elevate their privileges and their actions should be monitored.
db_accessadminMembers of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperatorMembers of the db_backupoperator fixed database role can back up the database.
db_ddladminMembers of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriterMembers of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareaderMembers of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriterMembers of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareaderMembers of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

Note: The permissions assigned to the fixed-database roles cannot be changed.

The following table explains the commands, views and functions for working with database-level roles.

FeatureTypeDescription
sp_helpdbfixedroleMetadataReturns a list of the fixed database roles.
sp_dbfixedrolepermissionMetadataDisplays the permissions of a fixed database role.
sp_helproleMetadataReturns information about the roles in the current database.
sp_helprolememberMetadataReturns information about the members of a role in the current database.
sys.database_role_membersMetadataReturns one row for each member of each database role.
IS_MEMBERMetadataIndicates whether the current user is a member of the specified Microsoft Windows group or Microsoft SQL Server database role.
CREATE ROLECommandCreates a new database role in the current database.
ALTER ROLECommandChanges the name or membership of a database role.
DROP ROLECommandRemoves a role from the database.
sp_addroleCommandCreates a new database role in the current database.
sp_droproleCommandRemoves a database role from the current database.
sp_addrolememberCommandAdds a database user, database role, Windows login, or Windows group to a database role in the current database.
sp_droprolememberCommandRemoves a security account from a SQL Server role in the current database.
GRANTPermissionsAdds permission to a role.
DENYPermissionsDenies a permission to a role.
REVOKEPermissionsRemoves a previously granted or denied permissions.

CREATE ROLE

Creates a new database role in the current database.

Syntax:

CREATE ROLE role_name [ AUTHORIZATION owner_name ]

Example:
Creating a database role that is owned by a fixed database role

The following example creates the database role auditors that is owned the db_securityadmin fixed database role.

CREATE ROLE auditors AUTHORIZATION db_securityadmin;
GO

ALTER ROLE

Adds or removes members to or from a database role, or changes the name of a user-defined database role.

Syntax:

ALTER ROLE role_name
ADD MEMBER database_principal | DROP MEMBER database_principal | 
WITH NAME = new_name [;]

Example:
The following example changes the name of role buyers to purchasing.

This example can be executed in the AdventureWorks sample database.

ALTER ROLE buyers WITH NAME = purchasing;

DROP ROLE

Removes a role from the database.

Syntax:

DROP ROLE [ IF EXISTS ] role_name

Example:
The following example drops the database role purchasing from the AdventureWorks2012 database.

DROP ROLE purchasing;
GO

sp_addrole

Creates a new database role in the current database.

Syntax:

sp_addrole [ @rolename = ] 'role' [ , [ @ownername = ] 'owner' ]

Example:
The following example adds a new role called Managers to the current database.

EXEC sp_addrole 'Managers';

sp_droprole

Removes a database role from the current database.

Syntax:

sp_droprole [ @rolename= ] 'role'

Example:
The following example removes the application role Sales.

EXEC sp_droprole 'Sales';
GO

sp_addrolemember

Adds a database user, database role, Windows login, or Windows group to a database role in the current database.

Syntax:

sp_addrolemember [ @rolename = ] 'role', [ @membername = ] 'security_account'

Example:
Adding a Windows login and Adding a database user

USE AdventureWorks2012;
GO
CREATE USER Mary5 FOR LOGIN [Contoso\Mary5] ;
GO
EXEC sp_addrolemember 'Production', 'Mary5';

sp_droprolemember

Removes a security account from a SQL Server role in the current database.

Syntax for both SQL Server and Azure SQL Database:

sp_droprolemember [ @rolename = ] 'role' , 
[ @membername = ] 'security_account'

Example:
The following example removes the user JonB from the role Sales.

EXEC sp_droprolemember 'Sales', 'Jonb';

GRANT

Grants permissions on a securable to a principal.

Syntax:

--Simplified syntax for GRANT  
GRANT { ALL [ PRIVILEGES ] }  
      | permission [ ( column [ ,...n ] ) ] [ ,...n ]  
      [ ON [ class :: ] securable ] TO principal [ ,...n ]   
      [ WITH GRANT OPTION ] [ AS principal ]

Arguments

  • ALL – Granting ALL is equivalent to granting the following permissions:
  • If the securable is a database, ALL means BACKUP DATABASE, BACKUP LOG, CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, and CREATE VIEW.
  • If the securable is a scalar function, ALL means EXECUTE and REFERENCES.
  • If the securable is a table-valued function, ALL means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
  • If the securable is a stored procedure, ALL means EXECUTE.
  • If the securable is a table, ALL means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
  • If the securable is a view, ALL means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

PRIVILEGES
Included for ISO compliance. Does not change the behavior of ALL.

permission
Is the name of a permission. The valid mappings of permissions to securables are described in the subtopics listed below.

column
Specifies the name of a column in a table on which permissions are being granted. The parentheses () are required.

class
Specifies the class of the securable on which the permission is being granted. The scope qualifier :: is required.

securable
Specifies the securable on which the permission is being granted.

TO principal
Is the name of a principal. The principals to which permissions on a securable can be granted vary, depending on the securable. See the subtopics listed below for valid combinations.

GRANT OPTION
Indicates that the grantee will also be given the ability to grant the specified permission to other principals.

AS principal
Use the AS principal clause to indicate that the principal recorded as the grantor of the permission should be a principal other than the person executing the statement.

Example:
— Execute the following as a database owner

GRANT EXECUTE ON TestProc TO TesterRole WITH GRANT OPTION;  
EXEC sp_addrolemember TesterRole, User1;  
-- Execute the following as User1  
-- The following fails because User1 does not have the permission as the User1  
GRANT EXECUTE ON TestMe TO User2;  
-- The following succeeds because User1 invokes the TesterRole membership  
GRANT EXECUTE ON TestMe TO User2 AS TesterRole;

DENY

Denies a permission to a principal. Prevents that principal from inheriting the permission through its group or role memberships.

Syntax for SQL Server and Azure SQL Database:

-- Simplified syntax for DENY  
DENY   { ALL [ PRIVILEGES ] } 
     | <permission>  [ ( column [ ,...n ] ) ] [ ,...n ]  
    [ ON [ <class> :: ] securable ] 
    TO principal [ ,...n ]   
    [ CASCADE] [ AS principal ]  
[;]

REVOKE

Removes a previously granted or denied permission.

Syntax for SQL Server and Azure SQL Database:

-- Simplified syntax for REVOKE  
REVOKE [ GRANT OPTION FOR ]  
      {   
        [ ALL [ PRIVILEGES ] ]  
        |  
                permission [ ( column [ ,...n ] ) ] [ ,...n ]  
      }  
      [ ON [ class :: ] securable ]   
      { TO | FROM } principal [ ,...n ]   
      [ CASCADE] [ AS principal ]

sp_helpdbfixedrole

Returns a list of the fixed database roles.

Syntax:

sp_helpdbfixedrole [ [ @rolename = ] 'role' ]

Example:
The following example shows a list of all fixed database roles.

EXEC sp_helpdbfixedrole;  
GO

sp_dbfixedrolepermission

Displays the permissions of a fixed database role.

Syntax:

sp_dbfixedrolepermission [ [ @rolename = ] 'role' ] 

Example:
The following query returns the permissions for all fixed database roles because it does not specify a fixed database role.

EXEC sp_dbfixedrolepermission;
GO

sp_helprole

Returns information about the roles in the current database.

Syntax:

sp_helprole [ [ @rolename = ] 'role' ]

Example:
The following query returns all the roles in the current database
.

EXEC sp_helprole

sp_helprolemember

Returns information about the direct members of a role in the current database.

Syntax:

sp_helprolemember [ [ @rolename = ] 'role' ]

Example:
The following example displays the members of the Sales role.

EXEC sp_helprolemember 'Sales';

Published by Abdul Samad

Having 17+ years of extensive experience in IT industry, enabled to enhance the team performance and maximize customer satisfaction by strategically managing calls and implementing process improvements. Demonstrated ability to solve problems, meets challenging goals, and expedites delivery. Skilled MSSQL administrator guide team during the crisis situation. Apply Creative thoughts process in re-designing the workflow system to eliminate duplication of effort and increase productivity.

Leave a Reply