Dynamics CRM 365 SQL查询安全角色具体权限明细[通俗易懂]

1、查询用户所拥有的安全角色：

select su.FullName,r.Name,bu.name bussinessname from SystemUserRoles sur
left join SystemUserBase su on su.SystemUserId = sur.SystemUserId
left join BusinessUnitBase bu on bu.BusinessUnitId=su.BusinessUnitId
left join Role r on r.RoleId=sur.RoleId
where su.IsDisabled=0

2、安全角色的具体权限明细：

SELECT DISTINCT
r.Name 安全角色名称
,COALESCE(e.OriginalLocalizedName, e.Name) AS [EntityName]
,CASE p.AccessRight
WHEN 32 THEN “Create-创建”
WHEN 1 THEN “Read-读”
WHEN 2 THEN “Write-写”
WHEN 65536 THEN “Delete-删除”
WHEN 4 THEN “Append-追加”
WHEN 16 THEN “AppendTo-追加到”
WHEN 524288 THEN “Assign-分派”
WHEN 262144 THEN “Share-共享”
ELSE “None”
END AS [Privilege]
,CASE (rp.PrivilegeDepthMask % 0x0F)
WHEN 1 THEN “User (Basic)-个人”
WHEN 2 THEN “Business Unit (Local)-业务部门”
WHEN 4 THEN “Parental (Deep)-上下级”
WHEN 8 THEN “Organization (Global)-组织”
ELSE “Unknown”
END AS [PrivilegeLevel]
,(rp.PrivilegeDepthMask % 0x0F) as [PrivilegeDepthMask]
,CASE WHEN e.IsCustomEntity = 1 THEN “Yes” ELSE “No” END AS [IsCustomEntity]
FROM Role AS r
INNER JOIN RolePrivileges AS rp
ON r.RoleId = rp.RoleId
INNER JOIN Privilege AS p
ON rp.PrivilegeId = p.PrivilegeId
INNER JOIN PrivilegeObjectTypeCodes AS potc
ON potc.PrivilegeId = p.PrivilegeId
INNER JOIN MetadataSchema.Entity AS e
ON e.ObjectTypeCode = potc.ObjectTypeCode
ORDER BY r.Name, [EntityName]

3、用户有用的实际权限总和（比如A用户有三个角色，那么这里就是取这三个角色的并集，并且取最高权限）

select su.FullName 用户名,t.EntityName 实体,t.Privilege 权限名称, CASE (max(t.PrivilegeLevel))
WHEN 1 THEN “User (Basic)-个人”
WHEN 2 THEN “Business Unit (Local)-业务部门”
WHEN 4 THEN “Parental (Deep)-上下级”
WHEN 8 THEN “Organization (Global)-组织”
ELSE “Unknown”
END 权限大小 from SystemUserRoles sur
left join SystemUserBase su on su.SystemUserId = sur.SystemUserId
left join BusinessUnitBase bu on bu.BusinessUnitId=su.BusinessUnitId
left join Role r on r.RoleId=sur.RoleId
left join (SELECT DISTINCT
r.Name 安全角色名称
,COALESCE(e.OriginalLocalizedName, e.Name) AS [EntityName]
,CASE p.AccessRight
WHEN 32 THEN “Create-创建”
WHEN 1 THEN “Read-读”
WHEN 2 THEN “Write-写”
WHEN 65536 THEN “Delete-删除”
WHEN 4 THEN “Append-追加”
WHEN 16 THEN “AppendTo-追加到”
WHEN 524288 THEN “Assign-分派”
WHEN 262144 THEN “Share-共享”
ELSE “None”
END AS [Privilege]
,rp.PrivilegeDepthMask % 0x0F
AS [PrivilegeLevel]
,(rp.PrivilegeDepthMask % 0x0F) as [PrivilegeDepthMask]
,CASE WHEN e.IsCustomEntity = 1 THEN “Yes” ELSE “No” END AS [IsCustomEntity]
FROM Role AS r
INNER JOIN RolePrivileges AS rp
ON r.RoleId = rp.RoleId
INNER JOIN Privilege AS p
ON rp.PrivilegeId = p.PrivilegeId
INNER JOIN PrivilegeObjectTypeCodes AS potc
ON potc.PrivilegeId = p.PrivilegeId
INNER JOIN MetadataSchema.Entity AS e
ON e.ObjectTypeCode = potc.ObjectTypeCode) as t on r.Name=t.安全角色名称
where su.IsDisabled=0

group by su.FullName,t.EntityName,t.Privilege

–行转列
select *
from (select su.FullName 用户名,t.EntityName 实体,t.Privilege 权限名称, CASE (max(t.PrivilegeLevel))
WHEN 1 THEN “User (Basic)-个人”
WHEN 2 THEN “Business Unit (Local)-业务部门”
WHEN 4 THEN “Parental (Deep)-上下级”
WHEN 8 THEN “Organization (Global)-组织”
ELSE “Unknown”
END 权限大小 from SystemUserRoles sur
left join SystemUserBase su on su.SystemUserId = sur.SystemUserId

left join BusinessUnitBase bu on bu.BusinessUnitId=su.BusinessUnitId
left join Role r on r.RoleId=sur.RoleId
left join (SELECT DISTINCT
r.Name 安全角色名称
,COALESCE(e.OriginalLocalizedName, e.Name) AS [EntityName]
,CASE p.AccessRight
WHEN 32 THEN “Create-创建”
WHEN 1 THEN “Read-读”
WHEN 2 THEN “Write-写”
WHEN 65536 THEN “Delete-删除”
WHEN 4 THEN “Append-追加”
WHEN 16 THEN “AppendTo-追加到”
WHEN 524288 THEN “Assign-分派”
WHEN 262144 THEN “Share-共享”
ELSE “None”
END AS [Privilege]
,rp.PrivilegeDepthMask % 0x0F
AS [PrivilegeLevel]
,(rp.PrivilegeDepthMask % 0x0F) as [PrivilegeDepthMask]
,CASE WHEN e.IsCustomEntity = 1 THEN “Yes” ELSE “No” END AS [IsCustomEntity]
FROM Role AS r
INNER JOIN RolePrivileges AS rp
ON r.RoleId = rp.RoleId
INNER JOIN Privilege AS p
ON rp.PrivilegeId = p.PrivilegeId
INNER JOIN PrivilegeObjectTypeCodes AS potc
ON potc.PrivilegeId = p.PrivilegeId
INNER JOIN MetadataSchema.Entity AS e
ON e.ObjectTypeCode = potc.ObjectTypeCode) as t on r.Name=t.安全角色名称
where su.IsDisabled=0

group by su.FullName,t.EntityName,t.Privilege) as tt pivot(max(权限大小) for 权限名称 in([Create-创建],[Read-读]
,[Write-写],[Delete-删除],[Append-追加],[AppendTo-追加到],[Assign-分派],[Share-共享]
))t

 4、用户拥有的具体权限明细表（未合并，A用户有三个角色，三个角色的权限都会展示出来）

select su.FullName,r.Name,bu.name bussinessname,t.EntityName,t.Privilege,t.PrivilegeLevel from SystemUserRoles sur
left join SystemUserBase su on su.SystemUserId = sur.SystemUserId

left join BusinessUnitBase bu on bu.BusinessUnitId=su.BusinessUnitId
left join Role r on r.RoleId=sur.RoleId
left join (SELECT DISTINCT
r.Name 安全角色名称
,COALESCE(e.OriginalLocalizedName, e.Name) AS [EntityName]
,CASE p.AccessRight
WHEN 32 THEN “Create-创建”
WHEN 1 THEN “Read-读”
WHEN 2 THEN “Write-写”
WHEN 65536 THEN “Delete-删除”
WHEN 4 THEN “Append-追加”
WHEN 16 THEN “AppendTo-追加到”
WHEN 524288 THEN “Assign-分派”
WHEN 262144 THEN “Share-共享”
ELSE “None”
END AS [Privilege]
,CASE (rp.PrivilegeDepthMask % 0x0F)
WHEN 1 THEN “User (Basic)-个人”
WHEN 2 THEN “Business Unit (Local)-业务部门”
WHEN 4 THEN “Parental (Deep)-上下级”
WHEN 8 THEN “Organization (Global)-组织”
ELSE “Unknown”
END AS [PrivilegeLevel]
,(rp.PrivilegeDepthMask % 0x0F) as [PrivilegeDepthMask]
,CASE WHEN e.IsCustomEntity = 1 THEN “Yes” ELSE “No” END AS [IsCustomEntity]
FROM Role AS r
INNER JOIN RolePrivileges AS rp
ON r.RoleId = rp.RoleId
INNER JOIN Privilege AS p
ON rp.PrivilegeId = p.PrivilegeId
INNER JOIN PrivilegeObjectTypeCodes AS potc
ON potc.PrivilegeId = p.PrivilegeId
INNER JOIN MetadataSchema.Entity AS e
ON e.ObjectTypeCode = potc.ObjectTypeCode) as t on r.Name=t.安全角色名称
where su.IsDisabled=0