none
Audit when users are added/deleted from TFS groups

    Question

  • I'm trying to find a way to audit tfs so that I can when users are added or deleted from the the project collection groups, such as the "Project Collection Administrators", or "Project Collection Release Administrators". 

    I will also need to audit when users are added to different team projects.  I'm fine with even having to look into sql tables I just need to know where to look.

    Wednesday, March 15, 2017 7:22 PM

Answers

  • Hi Shane,

    Thank you for posting here.

    I checked the database with the related tables and cannot find the ‘add or delete’ time stamp for users, there are no corresponding columns in the tables.

    We can only get the user list in specific group from the related tables, eg:

    USE [Tfs_Collection];
    
    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 = 'Project Collection Administrators'
    

    Or : SELECT * FROM [Tfs_Configuration].[dbo].[tbl_Identity]

    Best Regards.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 16, 2017 9:45 AM

All replies

  • Hi Shane,

    Thank you for posting here.

    I checked the database with the related tables and cannot find the ‘add or delete’ time stamp for users, there are no corresponding columns in the tables.

    We can only get the user list in specific group from the related tables, eg:

    USE [Tfs_Collection];
    
    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 = 'Project Collection Administrators'
    

    Or : SELECT * FROM [Tfs_Configuration].[dbo].[tbl_Identity]

    Best Regards.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 16, 2017 9:45 AM
  • That is not good.  This maybe something that keeps us from being able to use TFS Release Manager as we need to be able to query, when a persons access was changed/added or deleted.
    Tuesday, March 21, 2017 6:32 PM
  • Hi Shane,

    Thank you for posting here.

    Theoretically it should be stamped in somewhere, but it indeed cannot find time stamp in TFS database.

    Anyway, I help you submit a user voice here, you can go and vote it up to achieve it in future.

    Best Regards.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 22, 2017 11:57 AM