SQL script Get a list of Duties and Privileges based on Security Role

SQL script Get a list of Duties and Privileges based on Security Role

SQL script Get a list of Duties and Privileges based on Security Role 

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
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
WHERE secRole.AOTNAME = ''  // Add role name here
ORDER BY OBJECTTYPE, secRoleExplode.SECURITYSUBROLE