How to find everyone (and their groups) on TFS 2015

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
	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
	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)


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s