SQL Query to get Security roles, duties and privileges in D365FO

• All security roles 

            Select Name as SecurityRoleName FROM SecurityRole;

• All security roles to duties

    SELECT SECURITYROLE.Name as SecurityRole, SECURITYDUTY.NAME as Duty FROM SECURITYOBJECTCHILDREREFERENCES JOIN SECURITYROLE ON SECURITYOBJECTCHILDREREFERENCES.IDENTIFIER = SECURITYROLE.AOTNAME JOIN SECURITYDUTY ON SECURITYOBJECTCHILDREREFERENCES.CHILDIDENTIFIER = SECURITYDUTY.IDENTIFIER     WHERE SECURITYOBJECTCHILDREREFERENCES.OBJECTTYPE = 0 AND SECURITYOBJECTCHILDREREFERENCES.CHILDOBJECTTYPE = 1

order by SECURITYROLE.Name asc;

• All security roles with privileges 

    SELECT SECURITYROLE.Name as SecurityRole, SECURITYPRIVILEGE.NAME as Privileges FROM SECURITYOBJECTCHILDREREFERENCES JOIN SECURITYROLE ON SECURITYOBJECTCHILDREREFERENCES.IDENTIFIER = SECURITYROLE.AOTNAME JOIN SECURITYPRIVILEGE

        ON SECURITYOBJECTCHILDREREFERENCES.CHILDIDENTIFIER = SECURITYPRIVILEGE.IDENTIFIER WHERE SECURITYOBJECTCHILDREREFERENCES.OBJECTTYPE = 0 AND SECURITYOBJECTCHILDREREFERENCES.CHILDOBJECTTYPE = 2 order by SECURITYROLE.Name asc;

 

• All role-duty combination with privilege 

SELECT SECURITYROLE.Name as SecurityRole, SECURITYROLE.AOTNAME as RoleSystemName,     SECURITYDUTY.NAME AS Duty, SECURITYDUTY.IDENTIFIER as DutySystemName,     SECURITYPRIVILEGE.NAME as Privilege, SECURITYPRIVILEGE.IDENTIFIER as PrivilegeSystemName FROM SECURITYOBJECTCHILDREREFERENCES JOIN SECURITYROLE ON SECURITYOBJECTCHILDREREFERENCES.IDENTIFIER = SECURITYROLE.AOTNAME JOIN SECURITYDUTY ON SECURITYOBJECTCHILDREREFERENCES.CHILDIDENTIFIER = SECURITYDUTY.IDENTIFIER JOIN SECURITYOBJECTCHILDREREFERENCES Table1 on Table1.IDENTIFIER = SECURITYDUTY.IDENTIFIER JOIN SECURITYPRIVILEGE on Table1.CHILDIDENTIFIER = SECURITYPRIVILEGE.IDENTIFIER WHERE SECURITYOBJECTCHILDREREFERENCES.OBJECTTYPE = 0 AND SECURITYOBJECTCHILDREREFERENCES.CHILDOBJECTTYPE = 1 AND Table1.OBJECTTYPE = 1 AND Table1.CHILDOBJECTTYPE = 2 order by SECURITYROLE.Name asc;

 

Keep daxing!!