Dynamics 365 用户&角色&权限

来源 http://www.htsjk.com/Sql_Server/45124.html

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