CODEWITHSUNDEEP
sql-serverdynamics-crm-2013
Kanini
Kanini

Reputation: 1993

SQL for Retrieving all users and default team from CRM 2013

I need to be able to retrieve all users and the default team of the Business Unit to which they belong.

How do I identify the default team of a Business Unit apart from the same name being used for both the team and BU. Is there a flag which identifies it as a default team?

My required output is the following columns

Here is my query. 

SELECT dbo_SystemUser.DomainName, (dbo_Team.Name + ' ' + dbo_Team.TeamId)
FROM (dbo_Team INNER JOIN (dbo_TeamMembership INNER JOIN dbo_SystemUser ON dbo_TeamMembership.SystemUserId = dbo_SystemUser.SystemUserId) ON dbo_Team.TeamId = dbo_TeamMembership.TeamId) INNER JOIN dbo_BusinessUnit ON dbo_Team.BusinessUnitId = dbo_BusinessUnit.BusinessUnitId
WHERE dbo_Team.Name = dbo_BusinessUnit.Name

Upvotes: 1

Views: 1285

Answers (2)

Alex
Alex

Reputation: 23300

Team table has IsDefault column which is TRUE for a BU's default team.

It's also included in the FilteredTeam view.

Query that solves the OP issue:

-- Pay attention to the FROMs, tables ignore Security Roles.
-- To respect security, query the filtered views
-- (FilteredSystemUser, FilteredTeam, FilteredTeamMembership)
-- On the other hand, querying tables is much faster

SELECT U.DomainName as [Domain Name], T.Name as [Team Name], T.TeamId as [Team ID]
FROM SystemUser U 
    INNER JOIN TeamMembership TM ON U.SystemUserId = TM.SystemUserId
    INNER JOIN Team T ON TM.TeamId = T.TeamId
WHERE T.IsDefault = 1

Upvotes: 2

James Wood
James Wood

Reputation: 17562

This should do it.

SELECT FilteredSystemUser.domainname, FilteredTeam.name, FilteredTeam.teamid
FROM FilteredSystemUser
    INNER JOIN FilteredBusinessUnit ON FilteredSystemUser.businessunitid = FilteredBusinessUnit.businessunitid
    INNER JOIN FilteredTeam ON FilteredTeam.businessunitid = FilteredBusinessUnit.businessunitid
        WHERE FilteredTeam.isdefault = 1

You also need to be using the Filtered Views.

Upvotes: 1

Related Questions