Is it possible to list all users in a TFS group from SQL Server
I am trying to recover some group membership information from an old TFS 2010 server for which the application tier is no longer available (but the SQL back-end has not yet been deleted). I know there are command line programs to get security information but I am wondering if it is possible to get security information (specifically group membership) given only the database tables/views.
2 Solutions collect form web for “Is it possible to list all users in a TFS group from SQL Server”
Here’s a query I use to list all users and memberships within a TFS Collection.
Select Object1.DisplayName as Name, Object2.DisplayName as Membership From ADObjectMemberships Member1, ADObjects Object1, ADObjects Object2 Where Object1.ObjectSID = Member1.MemberObjectSID and Object2.ObjectSID = Member1.ObjectSID Order By Membership, Name
After poking around and some trial-and-error, I found that the following SQL seems to work
USE MyCollection; SELECT --grp.[SamAccountName] 'group_name', member.SamAccountName 'member_name' FROM [ADObjects] grp JOIN ADObjectMemberships om ON om.ObjectSID = grp.ObjectSID JOIN ADObjects member ON om.MemberObjectSID = member.ObjectSID WHERE grp.SamAccountName = 'MyGroup'