Dynamics CRM: FetchXML to Retrieve Access Team Members

The very smart folks over at PowerObjects recently posted a blog article on how to retrieve the membership of an Access Team using SQL.

When working in CRM Online or when we need to use the query for a view we have to make our SQL statements work as FetchXML. Here is the FetchXML that will retrieve Access Team members. You can edit it for the desired entity and place filtering as appropriate. In this case, we are retrieving all the Opportunity records and User records for the Opportunity Sales Teams (CRM’s default OOTB example of Access Teams.)

<fetch>
  <entity name="opportunity" >
    <attribute name="name" />
    <link-entity name="principalobjectaccess" from="objectid" to="opportunityid" link-type="inner" alias="poa" >
      <attribute name="objectid" alias="objectid" />
      <link-entity name="team" from="teamid" to="principalid" link-type="inner" >
        <link-entity name="teamtemplate" from="teamtemplateid" to="teamtemplateid" >
          <attribute name="teamtemplatename" />
        </link-entity>
        <link-entity name="teammembership" from="teamid" to="teamid" link-type="inner" intersect="true" >
          <link-entity name="systemuser" from="systemuserid" to="systemuserid" link-type="inner" >
            <attribute name="fullname" />
          </link-entity>
        </link-entity>
      </link-entity>
    </link-entity>
  </entity>
</fetch>