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.Tags: Database Role