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.

  • How to assure that certain data in SQL Server 2008 can only be accessed with my software?
  • DECODE( ) function in SQL Server
  • SQL Server A trigger to work on multiple row inserts
  • Generate view with X and Y from geometry type
  • Create a sql query that can handle multiple check box selections
  • how to create temp table based on column number?
  • Best way to copy a database (SQL Server 2008)
  • Find all strings that share at least X characters, order by likeness
  • Tracking the flow of XML nodes in SQL Server
  • How to generate inserts?
  • Concise/elegant method for duplicating a table's schema?
  • What is the SQL command to forcibly close all other connections to the a database
  • 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;
       --grp.[SamAccountName] 'group_name',
        member.SamAccountName 'member_name'
        [ADObjects] grp
        JOIN ADObjectMemberships om ON om.ObjectSID = grp.ObjectSID
        JOIN ADObjects member ON om.MemberObjectSID = member.ObjectSID
        grp.SamAccountName = 'MyGroup'
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.