Is it possible to list all users in a TFS group from SQL Server

Question

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.

Answer 1

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

Answer 2

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'