Skip to main content

Notifications

Announcements

🌸 Community Spring Festival 2025 Challenge & Updates🌸
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Duties and Privilege...
Finance | Project Operations, Human Resources, ...
Suggested answer

Duties and Privilege's are not going to be retrieved

(0) ShareShare
ReportReport
Posted on by Shabir Ahmad 3,110

static void Mzk_ExportUsersecurities_User123(Args _args)
{
SysExcelApplication xlsApplication;
SysExcelWorkBooks xlsWorkBookCollection;
SysExcelWorkBook xlsWorkBook;
SysExcelWorkSheets xlsWorkSheetCollection;
SysExcelWorkSheet xlsWorkSheet;
SysExcelRange xlsRange;
CustTable custTable;
int row = 1;
str fileName;

SecurityTask securityTask, securityTaskDuty, securityTaskPriv;
SecuritySubTask securitySubTask;
SecurityRoleTaskGrant securityRoleTaskGrant;
SecurityRole securityRole, securityRoleDuty,securityRolePriv, securityRoleM;
SecurityTaskEntryPoint SecurityTaskEntryPoint;
SecurableObject SecurableObject;
SecurityUserRole securityUserRole;
UserInfo userInfo;

SecurityTaskEntryPoint taskEntryPoint;
SecurityRole role;
SecurityRoleTaskGrant taskGrant;
SecuritySubTask subTask;
SecurityTask privilege;

str privName;
str dutyName;
str entrName;
str accessLevel;

boolean i = true,j = true;
;
fileName = "C:\\Temp\\AXrole.csv"; //Rename file
xlsApplication = SysExcelApplication::construct();
xlsWorkBookCollection = xlsApplication.workbooks();
xlsWorkBook = xlsWorkBookCollection.add();
xlsWorkSheetCollection = xlsWorkBook.worksheets();
xlsWorkSheet = xlsWorkSheetCollection.itemFromNum(1);
xlsWorkSheet.cells().item(row,1).value("User Id");
xlsWorkSheet.cells().item(row,2).value("User Role");
xlsWorkSheet.cells().item(row,3).value("Duty");
xlsWorkSheet.cells().item(row,4).value("Privilege AOT");
xlsWorkSheet.cells().item(row,5).value("Privilege Description");
xlsWorkSheet.cells().item(row,6).value("Entry Point");
xlsWorkSheet.cells().item(row,7).value("Permission");

row++;

while select * from securityRole
join securityUserRole
where securityUserRole.User like "USERNAME" //Enter user information here
&& securityUserRole.SecurityRole == securityRole.RecId
{
while select * from securityRoleTaskGrant
join * from securityTask
where securityRoleTaskGrant.SecurityRole == securityRole.RecId
&& securityTask.RecId == securityRoleTaskGrant.SecurityTask

{
if(securityTask.Type == SecurityTaskType::Duty)
{
while select SecurityTaskEntryPoint
join SecurableObject where SecurableObject.RecId == SecurityTaskEntryPoint.EntryPoint
join subTask where subTask.SecuritySubTask == SecurityTaskEntryPoint.SecurityTask
join taskGrant where taskGrant.SecurityTask == subTask.SecurityTask
join role where role.RecId == taskGrant.SecurityRole
&& role.AotName like securityRole.AotName

{
select privilege
where privilege.RecId == taskGrant.SecurityTask
&& SecurityTaskType::Duty == privilege.Type;
dutyName = privilege.AotName;

select privilege
where privilege.RecId == subTask.SecuritySubTask
&& SecurityTaskType::Privilege == privilege.Type;
privName = privilege.AotName;
{
//Information to Excel
xlsWorkSheet.cells().item(row,1).value(securityUserRole.User); //User id
xlsWorkSheet.cells().item(row,2).value(securityRole.Name); //Role Name
xlsWorkSheet.cells().item(row,3).value(dutyName); //DutyName
xlsWorkSheet.cells().item(row,4).value(privName); //Privilege AOT Name
xlsWorkSheet.cells().item(row,5).value(privilege.Name); //Privilege Description
xlsWorkSheet.cells().item(row,6).value(SecurableObject.Name); //Entry Points
xlsWorkSheet.cells().item(row,7).value(enum2str(SecurityTaskEntryPoint.PermissionGroup)); //Access Permission level
row++;
}
}
}
else //Role if contain Privilegs directly
{
while select * from SecurityTaskEntryPoint
join SecurableObject
where SecurableObject.RecId == SecurityTaskEntryPoint.EntryPoint
&& SecurityTaskEntryPoint.SecurityTask == securityTask.RecId
{
xlsWorkSheet.cells().item(row,1).value(securityUserRole.User); //User id
xlsWorkSheet.cells().item(row,2).value(securityRole.Name); //Role Name
xlsWorkSheet.cells().item(row,3).value(""); //DutyName
xlsWorkSheet.cells().item(row,4).value(securityTask.AotName); //Privilege AOT Name
xlsWorkSheet.cells().item(row,5).value(securityTask.Name); //Privilege Description
xlsWorkSheet.cells().item(row,6).value(SecurableObject.Name); //Entry Points
xlsWorkSheet.cells().item(row,7).value(enum2str(SecurityTaskEntryPoint.PermissionGroup)); //Access Permission level
row++;
}
}
}
}
if(WinApi::fileExists(fileName))
WinApi::deleteFile(fileName);
xlsWorkbook.saveAs(fileName);
xlsApplication.visible(true);
info(strFmt("File has been exported"));
}
RolesandPrivilegs.png

Hi Experts, 

After adding this logic i get only Header not rolese and Privileges as shown in exported excel sheet image. Note I used this code before but it was working right now not..

Best Regards,

Shabir Ahmad

  • Suggested answer
    faiz7049 Profile Picture
    faiz7049 2,215 on at
    RE: Duties and Privilege's are not going to be retrieved

    Hello Ahmed,

    Hope your issue have been resolved. Please verify the answer to close this thread.

    Thanks, 

  • faiz7049 Profile Picture
    faiz7049 2,215 on at
    RE: Duties and Privilege's are not going to be retrieved

    Try 

    static void Mzk_ExportUsersecurities_User(Args _args)
{
    SysExcelApplication         xlsApplication;
    SysExcelWorkBooks           xlsWorkBookCollection;
    SysExcelWorkBook            xlsWorkBook;
    SysExcelWorkSheets          xlsWorkSheetCollection;
    SysExcelWorkSheet           xlsWorkSheet;
    SysExcelRange               xlsRange;
    CustTable                   custTable;
    int                         row = 1;
    str                         fileName;

    SecurityTask                securityTask, securityTaskDuty, securityTaskPriv;
    SecuritySubTask             securitySubTask;
    SecurityRoleTaskGrant       securityRoleTaskGrant;
    SecurityRole                securityRole, securityRoleDuty,securityRolePriv, securityRoleM;
    SecurityTaskEntryPoint      SecurityTaskEntryPoint;
    SecurableObject             SecurableObject;
    SecurityUserRole            securityUserRole;
    UserInfo                    userInfo;

    SecurityTaskEntryPoint  taskEntryPoint;
    SecurityRole            role;
    SecurityRoleTaskGrant   taskGrant;
    SecuritySubTask         subTask;
    SecurityTask            privilege;

    str privName;
    str dutyName;
    str entrName;
    str accessLevel;

    boolean i = true,j = true;
    ;
    fileName = "C:\\Test/username.csv";    //Rename file
    xlsApplication           = SysExcelApplication::construct();
    xlsWorkBookCollection    = xlsApplication.workbooks();
    xlsWorkBook              = xlsWorkBookCollection.add();
    xlsWorkSheetCollection   = xlsWorkBook.worksheets();
    xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);
    xlsWorkSheet.cells().item(row,1).value("User Id");
    xlsWorkSheet.cells().item(row,2).value("User Role");
    xlsWorkSheet.cells().item(row,3).value("Duty");
    xlsWorkSheet.cells().item(row,4).value("Privilege AOT");
    xlsWorkSheet.cells().item(row,5).value("Privilege Description");
    xlsWorkSheet.cells().item(row,6).value("Entry Point");
    xlsWorkSheet.cells().item(row,7).value("Permission");

    row  ;

    while select * from securityRole
            join securityUserRole
            where securityUserRole.User like 'a.ahm*'  //Enter user information here
            && securityUserRole.SecurityRole == securityRole.RecId
        {
        while select * from securityRoleTaskGrant
        join * from securityTask
            where securityRoleTaskGrant.SecurityRole == securityRole.RecId
                && securityTask.RecId == securityRoleTaskGrant.SecurityTask

        {
            if(securityTask.Type == SecurityTaskType::Duty)
            {
               while select SecurityTaskEntryPoint
                            join SecurableObject where SecurableObject.RecId == SecurityTaskEntryPoint.EntryPoint
                            join subTask where subTask.SecuritySubTask == SecurityTaskEntryPoint.SecurityTask
                            join taskGrant where taskGrant.SecurityTask == subTask.SecurityTask
                            join role where role.RecId == taskGrant.SecurityRole
                                &&  role.AotName like securityRole.AotName

                {
                    select privilege
                        where privilege.RecId == taskGrant.SecurityTask
                        && SecurityTaskType::Duty == privilege.Type;
                            dutyName = privilege.AotName;

                    select privilege
                        where privilege.RecId == subTask.SecuritySubTask
                        && SecurityTaskType::Privilege == privilege.Type;
                        privName = privilege.AotName;
                    {
                       //Information to Excel
                        xlsWorkSheet.cells().item(row,1).value(securityUserRole.User);         //User id
                        xlsWorkSheet.cells().item(row,2).value(securityRole.Name);  //Role Name
                        xlsWorkSheet.cells().item(row,3).value(dutyName); //DutyName
                        xlsWorkSheet.cells().item(row,4).value(privName);  //Privilege AOT Name
                        xlsWorkSheet.cells().item(row,5).value(privilege.Name);  //Privilege Description
                        xlsWorkSheet.cells().item(row,6).value(SecurableObject.Name);  //Entry Points
                        xlsWorkSheet.cells().item(row,7).value(enum2str(SecurityTaskEntryPoint.PermissionGroup));  //Access Permission level
                        row  ;
                    }
                }
            }
            else             //Role if contain Privilegs directly
            {
                while select * from SecurityTaskEntryPoint
                            join SecurableObject
                            where SecurableObject.RecId == SecurityTaskEntryPoint.EntryPoint
                            && SecurityTaskEntryPoint.SecurityTask == securityTask.RecId
                {
                    xlsWorkSheet.cells().item(row,1).value(securityUserRole.User);         //User id
                    xlsWorkSheet.cells().item(row,2).value(securityRole.Name);  //Role Name
                    xlsWorkSheet.cells().item(row,3).value(""); //DutyName
                    xlsWorkSheet.cells().item(row,4).value(securityTask.AotName);  //Privilege AOT Name
                    xlsWorkSheet.cells().item(row,5).value(securityTask.Name);  //Privilege Description
                    xlsWorkSheet.cells().item(row,6).value(SecurableObject.Name);  //Entry Points
                    xlsWorkSheet.cells().item(row,7).value(enum2str(SecurityTaskEntryPoint.PermissionGroup));  //Access Permission level
                    row  ;
                }
            }
        }
    }
    if(WinApi::fileExists(fileName))
    WinApi::deleteFile(fileName);
    xlsWorkbook.saveAs(fileName);
    xlsApplication.visible(true);
    info(strFmt("File has been exported"));
}

    and share output.

  • Shabir Ahmad Profile Picture
    Shabir Ahmad 3,110 on at
    RE: Duties and Privilege's are not going to be retrieved

    666666666666666666666666666666666666666666666666666666666666.png

  • faiz7049 Profile Picture
    faiz7049 2,215 on at
    RE: Duties and Privilege's are not going to be retrieved

    Make sure user "a.ahmed" has also System user role.

    Kindly share screen shot of user "a.ahmed" role.

    Like below screen shot.

    6014.pastedimage1607239297111v2.jpeg

  • nmaenpaa Profile Picture
    nmaenpaa 101,158 Moderator on at
    RE: Duties and Privilege's are not going to be retrieved

    Hi Shabir,

    would it be possible to see your full code? Clearly you have something there that gets stuck forever.

    Also your code is quite inefficient since you have many nested while loops (while loop inside another while loop). Once you have it working, I suggest you try to change the statement into one joined statement. This way you could reduce the nr of database calls from thousands to one.

  • Shabir Ahmad Profile Picture
    Shabir Ahmad 3,110 on at
    RE: Duties and Privilege's are not going to be retrieved

    RolesandPrivilegs123.png

    faiz7049 ,

    I started it but it is busy from yesterday, there is no result.

    Best Regards,

    Shabir 

  • faiz7049 Profile Picture
    faiz7049 2,215 on at
    RE: Duties and Privilege's are not going to be retrieved

    Start with your user Id and share the result.

  • faiz7049 Profile Picture
    faiz7049 2,215 on at
    RE: Duties and Privilege's are not going to be retrieved

    Still not giving answer what I asked you. Before you was telling that you have System Administrator role and trying to get result but getting blank.

    4426.user.JPG

    I just asked you to add System user role under your Id and run the job. But still you did not do it.

  • Shabir Ahmad Profile Picture
    Shabir Ahmad 3,110 on at
    RE: Duties and Privilege's are not going to be retrieved

    All the above roles are assign to the userid, the id which perform this action of exporting roles and Privileges to excel sheet.

  • faiz7049 Profile Picture
    faiz7049 2,215 on at
    RE: Duties and Privilege's are not going to be retrieved

    Like

    pastedimage1607239297111v2.jpeg

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

🌸 Community Spring Festival 2025 Challenge & Updates🌸

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

WIN Power Platform Community Conference 2025 tickets!

Jonas ”Jones” Melgaard – Community Spotlight

We are honored to recognize Jonas "Jones" Melgaard as our April 2025…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 294,095 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 232,866 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,158 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans