All the groups

Sometimes it’s difficult to know who is in which TFS group. The groups keep getting bigger, some people change from one department to another, a group gets created for a particular set of special permissions, and so on, and so forth. Be it for auditing or just to keep things tidy and organized, knowing who is in which group can be a tiresome task by navigating group by group.

The query

For all that matters, let’s say [Tfs_Configuration] is our TFS 2015 configuration database. With this, you can get group membership recursively until you figure out who is in which group. Please note that this may not be the best/fastest way to do it, as I didn’t worry too much about optimization.

;WITH SelectLeaf AS
(
	SELECT g.InternalScopeId, g.DisplayName, gm.*, 0 as leaf from [Tfs_Configuration].[dbo].tbl_Group g WITH (NOLOCK)
		INNER JOIN [Tfs_Configuration].[dbo].tbl_GroupMembership gm  WITH (NOLOCK) on g.Id = gm.ContainerId
		INNER JOIN [Tfs_Configuration].[dbo].tbl_Group ig  WITH (NOLOCK) on gm.MemberId = ig.Id	
		WHERE gm.Active = 1 AND g.Active = 1
	UNION 
	SELECT g.InternalScopeId,  g.DisplayName, gm.*, 1 as leaf from [Tfs_Configuration].[dbo].tbl_Group g  WITH (NOLOCK)
		INNER JOIN [Tfs_Configuration].[dbo].tbl_GroupMembership gm  WITH (NOLOCK) on g.Id = gm.ContainerId
		WHERE gm.Active = 1 AND g.Active = 1
),
GroupLeaf AS 
(	
	SELECT InternalScopeId, DisplayName, ContainerId,MemberId,MIN(leaf) as Leaf 
	FROM SelectLeaf
	GROUP BY InternalScopeId, DisplayName, ContainerId,MemberId
),
GroupHierarchy AS 
(
	SELECT InternalScopeId, DisplayName, ContainerId, MemberId, Leaf FROM GroupLeaf WHERE Leaf=1
	UNION ALL
	SELECT f.InternalScopeId, f.DisplayName, f.ContainerId,g.MemberId,g.Leaf FROM GroupLeaf f
	INNER JOIN GroupHierarchy g on f.MemberId = g.ContainerId
)
SELECT DISTINCT g.DisplayName as GroupName, id.DisplayName AS MemberName FROM GroupHierarchy g
INNER JOIN [Tfs_Configuration].[dbo].tbl_Identity id on g.MemberId = id.Id
AND g.Leaf= 1

Bear with me on this one because it’s been a while and I had to analyze my query and trust on my memory.

The first CTE defines what is a just a member and what is a group, and sets this information to the Leaf column. The GroupLeaf CTE checks for empty sub-groups, that could have been handled as members in the first query. The last CTE, GroupHierarchy, pulls each member and join him with all the groups he belongs to. At last, we get the GroupName and the MemberName of all results that have Leaf = 1. (which are, indeed, members)