Dynamics crm系统SQL语句

查询角色对实体的权限
SELECT DISTINCT Role.name, EntityView.PhysicalName AS [Entity Name], CASE Privilege.AccessRight
WHEN 1 THEN ‘READ’ WHEN 2 THEN ‘WRITE’ WHEN 4 THEN ‘APPEND’ WHEN 16 THEN ‘APPENDTO’ WHEN 32 THEN ‘CREATE’
WHEN 65536 THEN ‘DELETE’ WHEN 262144 THEN ‘SHARE’ WHEN 524288 THEN ‘ASSIGN’
END AS [Access Level], CASE PrivilegeDepthMask WHEN 1 THEN ‘User’ WHEN 2 THEN ‘Business Unit’
WHEN 4 THEN ‘Parent: Child Business Unit’ WHEN 8 THEN ‘Organisation’ END AS [Security Level]
FROM RolePrivileges --角色权限
INNER JOIN Role ON RolePrivileges.RoleId = Role.roleid --角色
INNER JOIN PrivilegeObjectTypeCodes ON RolePrivileges.PrivilegeId = PrivilegeObjectTypeCodes.PrivilegeId --角色权限对象类型代码
INNER JOIN Privilege ON RolePrivileges.PrivilegeId = Privilege.PrivilegeId --权限
INNER JOIN EntityView ON EntityView.ObjectTypeCode = PrivilegeObjectTypeCodes.ObjectTypeCode --权限实体视图
ORDER BY Role.name, [Entity Name]
查询用户拥有那些角色
select domainname,lastname+firstname,name
from systemuserroles a --用户&角色n:n关系表
join systemuser aa on a.SystemUserId=aa.systemuserid–用户表
join Role bb on a.RoleId=bb.RoleId–角色表