In a previous post, I started exploring the TFS configuration database. Let's continue here by having a look at some more configuration objects and relationships.
TFS Config security objects
|
Referenced table
|
Primary column
|
Table
|
Foreign column
|
|
tbl_security_identity_cache
|
tf_id
|
tbl_gss_group_membership
|
member_id
|
|
tbl_security_identity_cache
|
tf_id
|
tbl_security_domain_groups
|
group_id
|
|
tbl_security_identity_cache
|
tf_id
|
tbl_security_membership_cache
|
container_id
|
|
tbl_security_identity_cache
|
tf_id
|
tbl_security_membership_cache
|
member_id
|
|
tbl_gss_groups
|
tf_id
|
tbl_gss_group_membership
|
parent_group_id
|
|
tbl_security_domain
|
domain_number
|
tbl_security_domain_groups
|
domain_number
|
|
tbl_security_domain
|
domain_number
|
tbl_security_projects
|
domain_number
|
|
tbl_security_identity_type
|
type_id (int)
|
tbl_security_identity_cache
|
type (tinyint)
|
The relationship diagram above suggests that we can query user logins, group-membership for specific projects by a query like the following:
|
SELECT TOP (100) PERCENT
login.display_name AS user_name,
proj.scope_name AS project,
login_grp.display_name AS user_group
FROM dbo.tbl_security_membership_cache AS membership INNER JOIN
dbo.tbl_security_identity_cache AS login ON membership.member_id = login.tf_id INNER JOIN
dbo.tbl_security_projects AS proj INNER JOIN
dbo.tbl_security_domain AS domain INNER JOIN
dbo.tbl_security_domain_groups AS grp ON domain.domain_number = grp.domain_number
ON proj.domain_number = domain.domain_number
ON membership.container_id = grp.group_id INNER JOIN
dbo.tbl_security_identity_cache AS login_grp ON grp.group_id = login_grp.tf_id
ORDER BY user_name, project
|
The above query may give us results similar to the following
|
user_name
|
project
|
user_group
|
|
Administrator
|
Research education "agile
|
Administrators
|
|
Administrator
|
Research education "agile
|
[TEAM FOUNDATION]\Team Foundation Service Accounts
|
|
Administrator
|
SourceSafeProjects
|
[TEAM FOUNDATION]\Team Foundation Service Accounts
|
|
Administrator
|
SourceSafeProjects
|
Administrators
|
|
Administrator
|
DefaultCollection
|
[TEAM FOUNDATION]\Team Foundation Service Accounts
|
|
Administrator
|
DefaultCollection
|
Administrators
|