So here's one of my favorite queries. It provides flat data that I consume into an Excel pivot table and let's our team gain visibility on the setup in many useful ways. Ignore the remark about "consumer" roles, those are custom to my environment and that is a good example of where we do not allow AX's "grant none means grant all" to function as designed.
-- build replacement for SECURITYUSERROLECONDITION that also contains implicit DATAAREA
SELECT urc.[PARTITION], urc.SECURITYUSERROLE, urc.DATAAREA
INTO #urc -- temporary table
FROM SECURITYUSERROLECONDITION urc
UNION ALL
SELECT ur.[PARTITION], ur.RECID AS SECURITYUSERROLE, d.ID AS DATAAREA
FROM SECURITYUSERROLE ur
JOIN MODELSECURITYROLELAYER srl ON srl.ROLEHANDLE = ur.SECURITYROLE AND srl.NAME NOT LIKE N'%Consumer' -- do not explode *Consumer roles
JOIN DATAAREA d ON d.ISVIRTUAL = 0 -- 1:n
WHERE NOT EXISTS (SELECT 1 FROM SECURITYUSERROLECONDITION urc WHERE urc.[PARTITION] = ur.[PARTITION] AND urc.SECURITYUSERROLE = ur.RECID);
SELECT DISTINCT -- eliminate duplicates caused by SECURITYROLEEXPLODEDGRAPH internal relation error
p.PARTITIONKEY,
p.NAME AS PARTITIONNAME,
u.ID AS USERID,
u.NAME AS USERNAME,
u.COMPANY AS USERDEFAULTCOMPANY,
u.NETWORKDOMAIN,
u.NETWORKALIAS,
CAST(u.[SID] AS NVARCHAR(80)) AS NETWORKSID,
rx.AOTNAME AS ROLEAOT,
rx.NAME AS ROLENAME,
rx.[DESCRIPTION] AS ROLEDESC,
sx.AOTNAME AS SUBROLEAOT,
sx.NAME AS SUBROLENAME,
sx.[DESCRIPTION] AS SUBROLEDESC,
urc.DATAAREA AS DATAAREAID,
d.NAME AS DATAAREANAME,
CAST(1 AS NUMERIC(32,16)) AS [GRANT]
FROM USERINFO u
JOIN SECURITYUSERROLE ur ON ur.[PARTITION] = u.[PARTITION] AND ur.USER_ = u.ID -- 1:n explode user across roles
JOIN #urc urc ON urc.[PARTITION] = ur.[PARTITION] AND urc.SECURITYUSERROLE = ur.RECID -- 1:n explode user/role across companies
JOIN DATAAREA d ON d.[PARTITION] = urc.[PARTITION] AND d.ID = urc.DATAAREA -- 1:1 translate company into name
JOIN [PARTITIONS] p ON p.RECID = d.[PARTITION] -- 1:1 translate partition into name
JOIN SECURITYROLEEXPLODEDGRAPH re ON re.SECURITYROLE = ur.SECURITYROLE -- 1:n explode role across sub-roles
JOIN SECURITYROLE_INLINEFUNC(@LanguageId) rx ON rx.RECID = re.SECURITYROLE -- 1:1 translate role handle into name and description
JOIN SECURITYROLE_INLINEFUNC(@LanguageId) sx ON sx.RECID = re.SECURITYSUBROLE -- 1:1 translate sub-role handle into name and description
WHERE u.[ENABLE] = 1
AND ur.ASSIGNMENTSTATUS = 1
AND rx.ISENABLED = 1
AND sx.ISENABLED = 1;
