Retrieving Security Information in Dynamics 365 F&O using SQL Queries

di

Very often in Dynamics 365 F&O it happens that you have to retrieve details relating to security such as, for example, the list of all security roles or duties etc.
So I would like to show you some SQL queries to retrieve this information.

The tables to consider are the following:

  • SECURITYROLES
  • SECURITYOBJECTCHILDREREFERENCES
  • SECURITYDUTY
  • SECURITYPRIVILEGE

Here are some very useful SQL queries:

-- List  of all security roles 
Select Name as SecurityRoleName FROM SecurityRole;

-- List of all security roles to duties
SELECT T2.Name as SecurityRole, T3.NAME as Duty 
FROM SECURITYOBJECTCHILDREREFERENCES T1 
JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME 
JOIN SECURITYDUTY T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER
WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 1 ;

-- List of all security roles with privileges 
SELECT T2.Name as SecurityRole, T3.NAME as Privileges
FROM SECURITYOBJECTCHILDREREFERENCES T1 
JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME 
JOIN SECURITYPRIVILEGE T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER
WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 2 ;

-- List of all role-duty combination with privilege 
SELECT T2.Name as SecurityRole, T2.AOTNAME as RoleSystemName,  T3.NAME AS Duty, T3.IDENTIFIER as DutySystemName, T5.NAME as Privilege, T5.IDENTIFIER as PrivilegeSystemName
FROM SECURITYOBJECTCHILDREREFERENCES T1 
JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME 
JOIN SECURITYDUTY T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER
JOIN SECURITYOBJECTCHILDREREFERENCES T4 on T4.IDENTIFIER = T3.IDENTIFIER
JOIN SECURITYPRIVILEGE T5 on T4.CHILDIDENTIFIER = T5.IDENTIFIER
WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 1

Lascia un commento