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.

3 comments:

  1. This is a great one. Thank you very much.
    Is there a way to show the Privilege/Duty descriptive name instead of the AOTname for tasks and sub-tasks ?

    ReplyDelete
    Replies
    1. Try this and see if it is what you want. Add the code below after line 37:
      JOIN ModelElementLabel label1
      ON secTAsk.name = '@' + label1.Module + CONVERT(NVARCHAR(20),label1.labelID)
      AND label1.Language = 'en_us'

      And add label1.text to your selection.

      Repeat this exercise for subtask (SecTask2).

      Delete
  2. Thank you for sharing! I’m now working on advertising management software project, this solution builds on the Microsoft dynamics architecture, so maybe I will need to get a list of duties and privileges based on a security role. In this case, the script will be extremely useful :)

    ReplyDelete