Creating a query to find all users with a specified security role

I ran into an interesting requirement this morning that let’s me show off one of my favorite CRM add-on utilities: Stunnware Tools.  Let’s cover both.

The Requirement

I needed to create a list of users that were members of a specific security role.  Rather than writing an application or designing a SQL query, I opened Stunnware Tools for Microsoft Dynamics CRM so that I could create a query in the FetchXML Wizard.

Using the FetchXML Wizard

The FetchXML Wizard allows you to query CRM by building a FetchXML query.  Here is a screen shot of the Designer:

Designing the Query

The following steps were required to created the desired query:

Step 1:

Query the SystemUser entity and return the Full Name of the user.

Step 2:

Include a Linked Entity that links the SystemUser Entity to the SystemUserRoles Entity

Step 3:

Add another Linked Entity from SystemUserRoles to Role.  This link will have a filter applied where the Name of the Role is equal to salesperson.

Here is the resulting FetchXML query:

<fetch mapping="logical" count="50" version="1.0">
  <entity name="systemuser">
    <attribute name="fullname" />
    <link-entity name="systemuserroles" from="systemuserid" to="systemuserid">
      <link-entity name="role" from="roleid" to="roleid">
        <filter>
          <condition attribute="name" operator="eq" value="salesperson" />
        </filter>
      </link-entity>
    </link-entity>
  </entity>
</fetch>

Which produces the following result set:

This whole process took me less than 5 minutes because the FetchXML Wizard Query Designer understands the links between CRM Entities and allowed me to quickly select those links and specify the necessary filter to produce the dataset I needed.

The Export to Excel module included in Stunnware Tools allows me to export the above result set to an Excel worksheet.

More About Stunnware Tools

There are two editions:

• The Community Edition: It's free and contains the Metadata Viewer, FetchXml Wizard and Excel Export.
• The Professional Edition: A subscription-based version with access to all tools of the Community Edition plus the Code Generator for C# and VB.NET, the CRM Help File Generator, additional features of the FetchXml wizard and the Customization Comparer.

Stunnware Tools is probably the most valuable Dynamics CRM add-on that I have.  I use it on a weekly, if not daily basis and which makes me a much more productive developer.