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.

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

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

Here’s the fetchxml.

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

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.

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.

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  Follow @CRMChartGuy