Thursday, November 27, 2014

AX2012 R2 Get a list of Duties and Privileges based on Security Role

This is a SQL script to get a list of duties and privileges based on a security role. I definitely saw something similar before, but couldn't find it again. So I'm putting it down here for reference.

USE [Model_database_name];

SELECT secRole.AOTNAME [Role_Name], secRoleExplode.SECURITYROLE, 
secRole2.AOTNAME [Subrole_Name], secRoleExplode.SECURITYSUBROLE, 
secTask.AOTNAME [Task_name], secRoleTask.SECURITYTASK, 
secTask2.AOTNAME [secTask2_name], secTaskExplode.SECURITYSUBTASK,
CASE
  WHEN secTask2.TYPE = 0 THEN 'Privilege'
  WHEN secTask2.TYPE = 1 THEN 'Duties'
  ELSE 'Other'
END AS OBJECTTYPE
--,secTaskEntryPoint.ENTRYPOINT, secObject.name, 
--CASE
--  WHEN secTaskEntryPoint.PERMISSIONGROUP = 0 THEN 'No access'
--  WHEN secTaskEntryPoint.PERMISSIONGROUP = 1 THEN 'Read'
--  WHEN secTaskEntryPoint.PERMISSIONGROUP = 2 THEN 'Update'
--  WHEN secTaskEntryPoint.PERMISSIONGROUP = 3 THEN 'Create'
--  WHEN secTaskEntryPoint.PERMISSIONGROUP = 4 THEN 'Correct'
--  WHEN secTaskEntryPoint.PERMISSIONGROUP = 5 THEN 'Delete'
--END AS [Access level], secObject.TYPE
FROM SECURITYROLE secRole
join SECURITYROLEEXPLODEDGRAPH secRoleExplode
ON secRole.RECID = secRoleExplode.SECURITYROLE
JOIN SECURITYROLE secRole2
ON secRoleExplode.SECURITYSUBROLE = secRole2.RECID
JOIN SECURITYROLETASKGRANT secRoleTask
ON secRoleExplode.SECURITYSUBROLE = secRoleTask.SECURITYROLE
JOIN SECURITYTASK secTask
ON secTask.RECID = secRoleTask.SECURITYTASK
JOIN SECURITYTASKEXPLODEDGRAPH secTaskExplode
ON secRoleTask.SECURITYTASK = secTaskExplode.SECURITYTASK
JOIN SECURITYTASK secTask2
ON secTaskExplode.SECURITYSUBTASK = secTask2.RECID
--JOIN SECURITYTASKENTRYPOINT secTaskEntryPoint
--ON secTaskEntryPoint.SECURITYTASK = secTask2.RECID
--JOIN SECURABLEOBJECT secObject
--ON secObject.RECID = secTaskEntryPoint.ENTRYPOINT
WHERE secRole.AOTNAME = 'HcmEmployee'
ORDER BY OBJECTTYPE, secRoleExplode.SECURITYSUBROLE
This posting is provided "AS IS" with no warranties, and confers no rights.