PowerObjects Blog 

for Microsoft Business Applications

|

How to Retrieve all the Access Team Members for all Accounts in Dynamics CRM

Post Author: Joe D365 |

A frequently asked question we get from users using Access Teams is: How do you join or link from the account record to the users on the account team in a query? The Access Team functionality is great, but it is not easily queried from the user interface - such as through Advanced Find. So, let's talk about how you would do this from SQL.

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 INNER JOIN
PrincipalObjectAccess AS poa ON a.AccountId = poa.ObjectId INNER JOIN
TeamBase AS ON poa.PrincipalId = t.TeamId INNER JOIN
TeamMembership AS tm ON t.TeamId = tm.TeamId INNER JOIN
SystemUser AS ON tm.SystemUserId = u.SystemUserId INNER JOIN
TeamTemplate AS tt ON t.TeamTemplateId = tt.TeamTemplateId
WHERE     (a.Name'Test Account')

Note: To retrieve all accounts, remove the Where Clause

AccountName

TeamName

FirstName

LastName

TeamTemplateName

Test Account

10fd1a1b-95e6-e311-a6da-005056a51e53+f433e998-39dc-e311-b4cc-005056a51e53

Paul

West

Account Access Team

Test Account

10fd1a1b-95e6-e311-a6da-005056a51e53+f433e998-39dc-e311-b4cc-005056a51e53

John

Mayer

Account Access Team

 

Hope this comes in handy! If you need additional tips and tricks, please feel free to check our multitude of Dynamics CRM blogs.

Happy CRM'ing!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

5 comments on “How to Retrieve all the Access Team Members for all Accounts in Dynamics CRM”

  1. Hi Joe,

    We have added members to a team using the CRM 2011 interface and SQL. Next, we shared a custom view with the team. The members that were added to the team using the interface could see the view. However, the member added to the team using SQL could not. I have verified that this user was, in fact, added to the team. Am I missing a step somewhere?

  2. Nevermind. we figured it out. There was another table in play that we had not considered. Thanks anyway.

  3. I need to get the team members under one account, how can i get them using Advanced find in CRM.?

  4. I just wanted to say Thanks for the post. Even though this was written a while ago it is still useful to those still on-prem. I have code that is adding and removing team members and now we have a way to ensure the code is running correct.

PowerObjects Recommends