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

on
  • 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;

Comments

Post a Comment