Featured Post

Dynamics 365 - Business Process Flow and Things to take note before you upgrade into Dynamics 365.

In 2 months ago, my company decided to upgrade the Dynamics CRM 2016 into Dynamics 365 and there is something different about Dynamics 365 B...

Thursday, November 16, 2017

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.

No comments:

Post a Comment