Retrieve all the Access Team members for all Accounts in Dynamic CRM

Description:

In this example we explain that how to Retrieve all the Access Team members for all Accounts in Dynamic CRM. Or how to get all the Sales Team Grid Member of the Opportunity of the Dynamics CRM.

SQL Query to get the Sales Team Member List of the Opportunity of Dynamic CRM.

To solve above all question first you have to understand the following Table in CRM.

·         PrincipalObjectAccess

·         TempBase

·         TeamMemberShip

·         TeamTemplate

·         SystemUser

Use the below query to retrieve all Team Member for Particular Accounts

SELECT    a.Name AS AccountName,a.AccountId, 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

Use the below query to retrieve all Sales Team Member for Particular Opportunity

select OpportunityId,

dbo.fn_new_GetSalesTeamForOpportunity(OpportunityId)  as 'Sales Team',

from Opportunity

below is the function that returns list of Sales Team member for particular Opportunity

CREATE FUNCTION fn_new_GetSalesTeamForOpportunity

(

    @OpportunityId UniqueIdentifier   

)

RETURNS VARCHAR(MAX)

AS

BEGIN

   return (SELECT STUFF((SELECT ',' + u.FirstName+u.LastName

FROM Opportunity AS a INNER JOIN

PrincipalObjectAccess AS poa ON a.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 a.OpportunityId = @OpportunityId

FOR XML PATH('')), 1, 1, '') as 'Sales Team')

END

GO