locked
TFS DB Query Project and User RRS feed

  • Question

  • Hi

    Currently I need to some statistic for our TFS 2013. I'm not an admin for TFS Server(So, I guess query by API is off the table), what I got is a read-only account for TFS DB.

    So, how can I get all users for a project by purely query the database?

    Wednesday, March 18, 2015 6:16 AM

Answers

  • In TFS 2013, to query team project’s users, please run below script in Tfs_Configuration database:

    select

    i.DisplayName,

    g.DisplayName,

    gs.Name

    from

    [dbo].[tbl_GroupMembership] gm

    inner join [dbo].[tbl_Identity] i

    on gm.MemberId = i.Id

    inner join [dbo].[tbl_Group] g

    on gm.ContainerId =g.Id

    inner join [dbo].[tbl_GroupScope] gs

    on g.InternalScopeId = gs.InternalScopeId

    where gs.Name='teamprojectname'


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Thursday, March 19, 2015 1:55 AM
    Moderator
  • Hi Jessica,  

    Thanks for your reply.

    That  tbl_security_identity_cache table has been changed in TFS 2013 databases, I have updated the query script in my initial reply, please check it.

    No, we cannot query the users from Warehouse database. Only the reportable fields or data will created in TFS Warehouse database.  


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, March 19, 2015 8:15 AM
    Moderator

All replies

  • In TFS 2013, to query team project’s users, please run below script in Tfs_Configuration database:

    select

    i.DisplayName,

    g.DisplayName,

    gs.Name

    from

    [dbo].[tbl_GroupMembership] gm

    inner join [dbo].[tbl_Identity] i

    on gm.MemberId = i.Id

    inner join [dbo].[tbl_Group] g

    on gm.ContainerId =g.Id

    inner join [dbo].[tbl_GroupScope] gs

    on g.InternalScopeId = gs.InternalScopeId

    where gs.Name='teamprojectname'


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Thursday, March 19, 2015 1:55 AM
    Moderator
  • I cannot see tbl_security_identity_cache table in both my Tfs_Collection DB and Tfs_Configuration DB. The link you provide cannot solve my problem. 
    Thursday, March 19, 2015 2:25 AM
  • There is a TFS_Warehouse DB that consolidate data from all collection. Can I query this info from it?
    Thursday, March 19, 2015 2:57 AM
  • Hi Jessica,  

    Thanks for your reply.

    That  tbl_security_identity_cache table has been changed in TFS 2013 databases, I have updated the query script in my initial reply, please check it.

    No, we cannot query the users from Warehouse database. Only the reportable fields or data will created in TFS Warehouse database.  


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, March 19, 2015 8:15 AM
    Moderator
  • Hi John,

    Instead of get users for one project, I'd like to get all projects and their users. 

    1.  List All Projects

    2.  List All Users 

    3. List All Project<-> User Relationship

    Is there any good way suggested? 


    Thursday, April 9, 2015 2:55 AM
  • Also I don't think the 'Name' field in tbl_GroupScope table means projectName. It does not match the ProjectName field in [tbl_Project] table in collection DB.
    Thursday, April 9, 2015 6:32 AM