Fixed: Users lose access to Team Projects after Tfs Server loses access to Active Directory

Recently, I completed a Tfs 2008 (with SP1) upgrade to Tfs 2012.4.  With no indications in the log files that there were errors.  I was doing my usual rounds of verification when I found that none of the users were showing up in the Tfs 2012.4 Team Project groups. 


It seems AD and Tfs Sync was out of whack.  I got that working pretty quickly, and restarted the TfsJobAgent and ensured that the AD Sync job ran successfully. 


I verified that the users still existed in the new Tfs 2012.4 instance, so now I just needed to get the users and groups they belonged to information from the Tfs 2008 instance and replicate them in the new Tfs 2012.4 instance.  With a lil T-SQL magic, this was not such a huge deal.


So, I hopped on over to the Tfs 2008 database server; connected to the TfsIntegration database and start a new query.  Ran the following T-SQL script, save the results as Tfs2008UserGroupsRestore.cmd file.


SELECT 'tfssecurity /g+ "[' + REPLACE(REPLACE(P.project_name, '>', '_'), '"', '-') + ']\' + A.groupName 
+ '" "UserDomain\' + A.userName + '" /collection:http://TfsServer:8080/tfs/DefaultCollection' AS Query
tbl_security_identity_cache_1.display_name as groupName,
tbl_security_identity_cache_1.Domain as domainName,
tbl_security_identity_cache.account_name AS userName
FROM tbl_security_membership_cache INNER JOIN
tbl_security_identity_cache ON tbl_security_membership_cache.member = tbl_security_identity_cache.sid INNER JOIN
tbl_security_identity_cache AS tbl_security_identity_cache_1 ON tbl_security_membership_cache.container = tbl_security_identity_cache_1.sid
WHERE (tbl_security_identity_cache_1.type = 4) AND (tbl_security_identity_cache_1.sid <> 'S-1-9-1551374245-1204400969-2402986413-2179408616-0-0-0-0-3'
and tbl_security_identity_cache.deleted<>1)
) AS A
INNER JOIN tbl_projects AS P ON
A.domainName LIKE '%'+ CONVERT(NVARCHAR(MAX), P.project_id) +'%'


Ran the .cmd file in an elevated Visual Studio 2012.4 command prompt on the Tfs 2012.4 app tier; and after a few minutes, like magic... All users have been restored to their groups.


I hope this post will be helpful to someone else in the future.  Cheers!





Comments (0)

Skip to main content