Chart your Security Roles in Dynamics 365

Sometimes it can be a little difficult to get a quick overview of who has got what security role in Dynamics 365. You either need to view the roles for each user manually, run one of the reports, or use a tool from the XrmToolBox.

However, charts can also be used to get a quick view of which roles are assigned to what users.

Roles per User stackedNumber of User per Role

Wait a minute! How can you chart on security roles if Dynamics 365 does not support charts on security roles?

Well, this is actually a user entity based chart that links through the N:N relationship to the security roles. I have actually written a blog post in the past on how to chart on N:N relationships, so when I set out to do this little helper chart, I simply followed the process there. N:N Relationship Chart in Dynamics 365

Check out that post for full step by step instructions. This post is just the cliff notes for this setup.

I figured I would make the security role charts available for download so anyone can take advantage of them and have a closer look at how they were put together. Download link is at the bottom of this post.

Here’s a quick description of how the fetchxml works for each of the charts.

Number of Roles per User Chart

Number of Roles per User

Answers how many roles each user has assigned, incl. users with zero roles. This chart has drill-down retained.

Here’s the fetchxml.
Dynamics 365 chart on Security Roles and User

Starting point is the systemuser entity. From there, a nested link-entity goes to the systemuserroles entity, to the role entity. The systemuserroles entity is the system created intersect table between users and their security roles. The aggregate is a “countcolumn” on the role entity. Using just “count” would give us inaccurate results.

The GroupBy is placed outside the nested link-entity and is on the fullname of the user.

Number of Users per Role

Number of User per Role

Answers how many users are assigned to each of the different security roles? Chart only includes the count of the users that are in your current view.

Here’s the fetchxml.
Dynamics 365 chart on Security Roles and Users

Setup is similar to the previous chart, but the GroupBy is placed inside the nested link-entity and groups by the security role name. Since we are grouping by a property on the related entity, drill-down is not retained on this chart.

Roles per User Stacked

Overview per user with their count of roles assigned, stacked by the role name.

This chart gives you a quick overview of who has got what role and you can quickly see if a given user is missing a role. Or if they have too many roles.

Here’s the fetchxml.
Dynamics 365 on Security Roles and Users stacked

Now since this is a stacked chart we are adding an extra GroupBy to the mix. The first GroupBy is outside the nested link-entity and is on the fullname of the user. The secondary GroupBy is inside the nested link-entity and is on the role name. The GroupBy attribute should always be placed on the entity where its property is located.

For this chart I included the categorycollection in the screenshot. It was identical in the previous charts. However, I wanted to show it here because it defines which GroupBy is primary by having it alias in the category. Note that the secondary GroupBy is not included in that section. It is implied which one is secondary in this case. You can only have two GroupBy attributes so it is implied which one is secondary.

Here’s a link to a folder where you can download the charts. Just import them to the user entity and they should be ready to go.

Disclaimer: Charts are provided for fun and games only. Please do your own due diligence if relying on these in a production environment. 

Once I was done with the fetch I used the Advanced Chart Editor for the XrmToolBox to style the charts a little and add the “CRM Chart Guy” watermark.

Hope you find the charts for users and their security roles helpful and feel free to throw some ideas in the comments.

As always, please sign up for my newsletter and follow me on Twitter  

11 thoughts on “Chart your Security Roles in Dynamics 365

  1. Nice!!! Always appreciated. Where would the world of Microsoft CRM be without your brilliant and tireless effort ?

  2. The charts don’t allow the ability to drill down by only showing the results by clicking on an individual bar. What is the logic needed to add the drill-down capability?

  3. All I can say is appreciation for what you do for the community. I have been trying to build this in normal Crm and listened the CRM Audio. Boom! Answer found.

    Will have to brush up on my code skills but This was awesome blog.

    • You can retrieve the permissions via FetchXML, which means it is possible. But I am not sure if it is possible to make it look nice, unless you are limiting yourself to a very few key permissions. There’s gonna be some detective work on getting the fetch build out, so I would definitely recommend using the FetchXML Builder in the XrmToolBox for it.

  4. Dear Ulrik,

    I am trying to map the security roles that were assigned to Teams.

    Unfortunately I cant come up with a solution. The Chart actually Shows the Team names on the left side – like your security role user Chart – but the Information about the connected Teams just won’t appear and im Having no more luck the the XML fetch builder.

    Would be great if you could take a look and maybe give a hint into the right direction

    Best regards


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s