Showing posts with label Security roles. Show all posts
Showing posts with label Security roles. Show all posts

Wednesday, April 2, 2025

How to add Admin role to user from SQL in D365FO

 How to add Admin role to user from SQL in D365FO

In SecurityRole table we will find all roles.

Execute the below statement to know the system admin role Recid.

Select Recid,* from SecurityRole where NAME like 'sys%'


Insert the data into SECURITYUSERROLE  table with username and role recid. Execute the below statement.

insert into SECURITYUSERROLE (USER_, SECURITYROLE,ASSIGNMENTSTATUS,ASSIGNMENTMODE)
values('Kishore', 235,1,1)

If you want to update you can use the below statement.

update SECURITYUSERROLE
set SECURITYROLE = 235 where  USER_ = 'Kishore'

Reference : Click here


Keep Daxing!!





Tuesday, September 21, 2021

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!!