When a CRM implementation goes live the most important task is to get the users on board with their new Dynamics CRM system. CRM Admins ensure that proper security roles are assigned to every user so that they can log in without any issue. However, assignment of the security roles to the users is still a manual task and when you have hundreds of users, there is a possibility that some users may get missed.
CRM has a very useful view on the User entity called Users with no assigned security roles which does a pretty good job in quickly identifying those users who have a valid license but do not any security role.
But the situation becomes challenging when you have blanket security roles e.g. ClickDimensions User or Xperido User that are assigned to every user and on top of that each user should have at least one CRM specific security role e.g. SalesPerson, Marketing Professional, etc.
How do you make sure that every user has at least one CRM security role on top of the blanket (or addin) security roles?
I would like to share a solution I have used to achieve this using FetchXml left outer join.
The prerequisite for this query is that you need to find out the GUIDs of the blanket security roles that you want to exclude from your search criteria.
e.g. the below query return all users who do not have any security role assigned to them excluding Xperido User and ClickDimensions User.
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >
<entity name="systemuser" >
<attribute name="fullname" />
<attribute name="internalemailaddress" />
<order attribute="fullname" descending="false" />
<filter type="and" >
<condition attribute="isdisabled" operator="eq" value="0" />
<condition entityname="userrole" attribute="roleid" operator="null" />
</filter>
<link-entity name="systemuserroles" alias="userrole" from="systemuserid" to="systemuserid" link-type="outer" >
<attribute name="roleid" />
<filter type="and" >
<condition attribute="roleid" operator="ne" value="{24EB9173-7C62-E511-80EA-C4346BC5A710}" /> <!--Xperido -->
<condition attribute="roleid" operator="ne" value="{26EC7898-1029-E411-934F-00155D00730E}" /> <!-- ClickDimensions -->
</filter>
</link-entity>
</entity>
</fetch>
You can run this fetchXml in XrmToolbox or save it as view (SavedQuery)