Fetch xml and SQL Query for Access team

Retrieve all user for a account  who belong to access team

Understanding CRM data structure is vital in this case. From a data structure, the following tables would be utilized:

• PrincipalObjectAccess
• TeamBase
• TeamMembership
• TeamTemplate
• SystemUser

Use this SQL Query to retrieve all Access Team Members for Account(s):

SELECT    a.Name AS AccountName, t.Name AS TeamName, u.FirstName, u.LastName, tt.TeamTemplateName

FROM   Account AS a INNER JOIN

PrincipalObjectAccess AS poa ON a.AccountId = poa.ObjectId INNER JOIN

TeamBase AS t ON poa.PrincipalId = t.TeamId INNER JOIN

TeamMembership AS tm ON t.TeamId = tm.TeamId INNER JOIN

SystemUser AS u ON tm.SystemUserId = u.SystemUserId INNER JOIN

TeamTemplate AS tt ON t.TeamTemplateId = tt.TeamTemplateId

WHERE   (a.Name = 'China')

 @"<fetch>
                             <entity name='account' >
                               <attribute name='name' />
                                   <link-entity name='principalobjectaccess' from='objectid' to='accountid' 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>
                                     <filter type='and'>
                                       <condition attribute='name' operator='eq' value='China' />
                                        </filter>   

                                  </entity>
                                </fetch>";


Reference -
http://www.powerobjects.com/2015/05/14/how-to-retrieve-all-the-access-team-members-for-all-accounts/

http://mscrmtechie.blogspot.in/2015/12/retrieve-access-team-members-using.html