T-SQL筆記59_條列角色與對應成員清單_檢查某個角色下授權的存取清單

條列角色與對應成員清單

WITH RoleMembers (member_principal_id, role_principal_id) 
AS 
(
  SELECT 
   rm1.member_principal_id, 
   rm1.role_principal_id
  FROM sys.database_role_members rm1 (NOLOCK)
   UNION ALL
  SELECT 
   d.member_principal_id, 
   rm.role_principal_id
  FROM sys.database_role_members rm (NOLOCK)
   INNER JOIN RoleMembers AS d 
   ON rm.member_principal_id = d.role_principal_id
)
select distinct rp.name as database_role, mp.name as database_userl
from RoleMembers drm
  join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)
  join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
order by rp.name

檢查某個角色下授權的存取清單(以USER_TABLE來篩)

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] = '角色名稱' 
and so.[type_desc]='USER_TABLE' 
and so.name='指定Table名'

so.[type_desc] 也可以更換為 SQL_STORED_PROCEDURE

此時就表示要以Stored Procedure篩查

第二種寫法多DB主機

;with cte_list as (
SELECT 
@@Servername as ServerName
,DB_NAME() AS DatabaseName
,d.name AS DatabaseUser
,ISNULL(dr.name, 'Public') AS DatabaseRole
,dp.permission_name as AdditionalPermission
,dp.state_desc AS PermissionState
,ISNULL(o.type_desc, 'N/A')  AS ObjectType
,ISNULL(o.name, 'N/A') AS ObjectName
FROM sys.database_principals d
    LEFT JOIN sys.database_role_members r
        ON d.principal_id = r.member_principal_id 
    LEFT JOIN sys.database_principals dr
        ON r.role_principal_id = dr.principal_id 
    left JOIN   sys.database_permissions dp
        ON d.principal_id = dp.grantee_principal_id
    LEFT JOIN sys.objects o
        ON dp.major_id = o.object_id 
)
select * from  cte_list where DatabaseName='資料庫名' and DatabaseRole='角色名' 

針對特定表確認Table層級存取權限清單列表

EXEC sp_table_privileges @table_name = '指定表名';