Dynamics 365 : Access Team in SQL

Few months ago, my team and I implemented a plugin,  based on certain conditions, the plugin will add or remove users from access team.

Today, we've found out the plugin was not working properly after upgraded to Dynamics 365. Thus , we have to fix it. 

We decided to retrieve all the affected records and access team members but it is not easily to retrieve the records through advanced find. So, we have to write the sql query to generate the list.

DEMO

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

FROM       filteredopportunity AS o INNER JOIN

PrincipalObjectAccess AS poa ON o.opportunityid = 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     (o.name = 'test 1') 

I hope this will save you some time and Happy CRM-ing.