獲取 SQL 角色的權限列表

我有一個名為的角色db_executor，需要找出它對哪些對象具有哪些權限。我發現列出給定角色的所有權限？但遠端工作的唯一解決方案是：

SELECT DB_NAME() AS 'DBName'
     ,p.[name] AS 'PrincipalName'
     ,p.[type_desc] AS 'PrincipalType'
     ,p2.[name] AS 'GrantedBy'
     ,dbp.[permission_name]
     ,dbp.[state_desc]
     ,so.[Name] AS 'ObjectName'
     ,so.[type_desc] AS 'ObjectType'
 FROM [sys].[database_permissions] dbp LEFT JOIN [sys].[objects] so
   ON dbp.[major_id] = so.[object_id] LEFT JOIN [sys].[database_principals] p
   ON dbp.[grantee_principal_id] = p.[principal_id] LEFT JOIN [sys].[database_principals] p2
   ON dbp.[grantor_principal_id] = p2.[principal_id]

WHERE p.[name] = 'db_executor'

問題是ObjectNameandObjectType是NULL. 因此，雖然我知道它僅在我不知道適用於哪些對象EXECUTE的狀態下才具有權限。GRANT有沒有更好的方法來獲取此列表，或者如何修改此程式碼以列出對象？

sys.types該腳本缺少用於獲取表類型資訊的左連接，這也需要執行權限才能使用（或者 join sys.table_types，您將獲得相同的數據）。嘗試以下操作：

SELECT  DB_NAME() AS 'DBName' ,
   p.[name] AS 'PrincipalName' ,
   p.[type_desc] AS 'PrincipalType' ,
   p2.[name] AS 'GrantedBy' ,
   dbp.[permission_name] ,
   dbp.[state_desc] ,
   CASE WHEN [dbp].[class_desc] = 'DATABASE' THEN 'DATABASE'
        ELSE COALESCE(so.[name], t.name)
   END AS 'ObjectName' ,
   CASE WHEN [dbp].[class_desc] = 'DATABASE' THEN 'DATABASE'
        ELSE COALESCE(so.[type_desc], N'TYPE')
   END AS 'ObjectType' 
FROM    [sys].[database_permissions] dbp
       LEFT JOIN [sys].[all_objects] so ON dbp.[major_id] = so.[object_id]
       LEFT JOIN [sys].[database_principals] p ON dbp.[grantee_principal_id] = p.[principal_id]
       LEFT JOIN [sys].[database_principals] p2 ON dbp.[grantor_principal_id] = p2.[principal_id]
       LEFT JOIN [sys].[types] t ON dbp.major_id = t.user_type_id
WHERE   p.[name] = 'db_executor';

引用自：https://dba.stackexchange.com/questions/155015