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

  • Article

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:https://TfsServer:8080/tfs/DefaultCollection' AS Query
 FROM 
 (
 SELECT 
 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!

 

...
Chev