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'
) 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!