User Roles Export

(0) Share

Report

Posted on by dynamicsgs

190

Hello,

I want to export an excel file/file which can show the security roles of each user we currently have. Is it possible to do this in an efficient way instead of clicking into each individual user and taking down what roles they possess?

Any help would be greatly appreciated.

Thank you in advance.

All responses (6)

Answers (2)

jork 12 on at

Like (0)

Report

User Roles Export

If you have access the backend via SSMS (connecting via Azure Active Directory - Universal with MFA), the below SQL script is what I use for exporting active users with their roles:

SELECT
   U.SystemUserId,
   U.FullName,
   U.AccessModeName,
   U.DomainName,
   U.InternalEmailAddress,
   U.Title,
   RC.UserRoleCount,
   STUFF((SELECT DISTINCT '; ' + R.Name
           FROM systemuserroles SUR INNER JOIN role R ON R.roleid = SUR.roleid
           WHERE SUR.systemuserid = U.systemuserid
           For XML Path('')), 1, 2,'') AS AssignedSecurityRoles
FROM
   systemuser U
   LEFT JOIN (SELECT SystemUserId, COUNT(1) AS UserRoleCount FROM systemuserroles GROUP BY SystemUserId) RC ON RC.systemuserid = U.systemuserid
WHERE
   U.IsDisabled = 0
   --AND U.fullname NOT LIKE '#%'
   --AND RC.UserRoleCount > 0
ORDER BY
   U.Fullname
Suggested answer

Ysondh 5 on at

Like (0)

Report

RE: User Roles Export

Great read and very helpful. Thanks alot.
[quote user="dynamicsgs"]
Hello,

I want to export an excel file/file which can show the security roles of each user we currently have. Is it possible to do this in an efficient way instead of clicking into each individual user and taking down what roles they possess?

Any help would be greatly appreciated.

Thank you in advance.
[/quote]
[quote user="dynamicsgs"]
Hello,

I want to export an excel file/file which can show the security roles of each user we currently have. Is it possible to do this in an efficient way instead of clicking into each individual user and taking down what roles they possess?

Any help would be greatly appreciated.

Thank you in advance.
[/quote]
[quote user="dynamicsgs"]
Hello,

I want to export an excel file/file which can show the security roles of each user we currently have. Is it possible to do this in an efficient way instead of clicking into each individual user and taking down what roles they possess?

Any help would be greatly appreciated.

Thank you in advance.
[/quote]
technology101010 35 on at

Like (0)

Report

RE: User Roles Export

Hello,

This is very helpful. Is there any way to filter the users who used a specific entity like for example Leads?

Thanks!

Tech101010
dynamicsgs 190 on at

Like (0)

Report

RE: User Roles Export

Hi Lu,

This is a great help. Thank you so much.
Verified answer

LuHao 40,872 on at

Like (2)

Report

RE: User Roles Export

Hi partner,

Basically, there are two ways.

1. We can use OOTB report to export Excel.

Open Advanced Find, Look for Reports, set condition: Name Equals "User Summary", click Results.

Open the report.

Select the user scope and click Run Report.

You will see the security roles of all users. Then save as Excel.

This report, however, does not show the security roles directly in one column, but rather many columns in which each user corresponds to his or her security role.

So if you want to display security roles directly in one column, you need to use XrmToolBox's "Users, Team and Security Role Report" tool.

2. XrmToolBox's "Users, Team and Security Role Report" tool

This is the download page for XrmToolBox: https://www.xrmtoolbox.com/

After downloading and unzipping it, open XrmToolBox.exe.

Connect to your D365 organization. Install "Users, Team and Security Role Report" in the tool library.

Open the tool and click Load Data, then click Generate Report. It will download two files, one is .csv and one is .xlsx.

Use Notepad to open the .csv file and copy all. Then open the .xlsx file and paste the data in the first worksheet.

The effect is as follows:
Verified answer

Gary Letter 1,267 on at

Like (0)

Report

RE: User Roles Export

Hi dynamicsgs,

In the Reports area, you can run the User Summary report to show all the security roles assigned to each user.

Gary Letter
NextLevel Business Systems

If you find the answer useful, please click "Yes" to mark it as verified.