We now have the ability to define a User Defined Server Level Role in SQL Server 2012 and higher versions. For the security and integrity of the data stored in a SQL Server database, managing permissions is essential. Assigning permissions to user-defined database roles is a frequent practice that streamlines permission management by combining users with similar rights. In this blog post, we’ll look at how to retrieve the permissions given to a certain user-defined database role in SQL Server using the provided T-SQL code.
USE [AdventureWorks2022];
Go
-- Retrieve the relevant information from system views
SELECT
SERVERPROPERTY('MachineName') AS ServerName, -- Name of the server machine
DB_NAME() AS DatabaseName, -- Name of the current database
p.[name] AS RoleName, -- Name of the role
p.[type_desc] AS RoleType, -- Type of the role
p.create_date AS RoleCreationDate, -- Creation date of the 'db_executor' role
p2.[name] AS GrantedBy, -- Principal (user or role) who granted the permission
dbp.[permission_name] AS PermissionName, -- Name of the granted permission
dbp.[state_desc] AS PermissionState, -- State of the permission (e.g., GRANT, WITH GRANT)
so.[Name] AS ObjectName, -- Name of the object (table, view, stored procedure) to which the permission is granted
so.[type_desc] AS ObjectType -- Type of the object (e.g., USER_TABLE, VIEW, SQL_STORED_PROCEDURE)
FROM
sys.database_permissions dbp -- Retrieve permissions information
LEFT JOIN
sys.objects so ON dbp.major_id = so.object_id -- Join with objects to retrieve object names
LEFT JOIN
sys.database_principals p ON dbp.grantee_principal_id = p.principal_id -- Join with principals to retrieve role names
LEFT JOIN
sys.database_principals p2 ON dbp.grantor_principal_id = p2.principal_id -- Join with principals to retrieve granted-by names
WHERE
p.[name] = 'db_executor'; -- Filter results for the 'db_executor' role
Keep in mind that built-in roles may not show any permissions because SQL Server has specified default permissions for them.
You may easily find the permissions granted to a user-defined database role in SQL Server by using the provided T-SQL code. This information is extremely useful for understanding and maintaining access control in your database system. You can improve security, simplify permission management, and maintain a strong data governance framework by organizing users into roles and allocating access accordingly.