我到处搜索过,并没有找到这个问题的确凿答案.
我需要一个可以为关联角色提供所有权限的脚本.
有什么想法,或者甚至是可能的？
这让我感到很紧张 – 但我似乎无法翻转它并给出角色的摘要,而不是用户.
WITH perms_cte as
(
select USER_NAME(p.grantee_principal_id) AS principal_name,dp.principal_id,dp.type_desc AS principal_type_desc,p.class_desc,OBJECT_NAME(p.major_id) AS object_name,p.permission_name,p.state_desc AS permission_state_desc
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
)
--role members
SELECT rm.member_principal_name,rm.principal_type_desc,p.object_name,p.permission_state_desc,rm.role_name
FROM perms_cte p
right outer JOIN (
select role_principal_id,dp.type_desc as principal_type_desc,member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*
from sys.database_role_members rm
INNER JOIN sys.database_principals dp
ON rm.member_principal_id = dp.principal_id
) rm
ON rm.role_principal_id = p.principal_id
order by 1
解决方法
我们提出了这个,似乎有效：
SELECT DISTINCT rp.name,ObjectType = rp.type_desc,PermissionType = pm.class_desc,pm.permission_name,pm.state_desc,ObjectType = CASE
WHEN obj.type_desc IS NULL
OR obj.type_desc = 'SYSTEM_TABLE' THEN
pm.class_desc
ELSE obj.type_desc
END,s.Name as SchemaName,[ObjectName] = Isnull(ss.name,Object_name(pm.major_id))
FROM sys.database_principals rp
INNER JOIN sys.database_permissions pm
ON pm.grantee_principal_id = rp.principal_id
LEFT JOIN sys.schemas ss
ON pm.major_id = ss.schema_id
LEFT JOIN sys.objects obj
ON pm.[major_id] = obj.[object_id]
LEFT JOIN sys.schemas s
ON s.schema_id = obj.schema_id
WHERE rp.type_desc = 'DATABASE_ROLE'
AND pm.class_desc <> 'DATABASE'
ORDER BY rp.name,rp.type_desc,pm.class_desc
