TFS Recovering Shelveset for Invalid User

One of the developers on the team was getting a TFS error (below) yesterday while trying to access a shelveset for a developer who left the account a couple of months ago.  Turns out he needed some of the code on the shelveset.

 TF50605: There was an error looking up the SID for TC30014  

Note to self...shelvesets are probably not the way to do this, thinking that a branch would better construct.  I want to encourage them to be doing more of these anyway.

The problem is because TFS is going to lookup this user in the Active Directory and the user does not exist anymore.  I can see the shelvesets in TFS using either TFS itself or TFS sidekicks.  I included a screen print of all the active shelvesets for this user in sidekicks.



So TFS must be doing some lookup on the user and when it does not find it - errors out.  Not knowing how to solve this, I put couple of searches later ("tf50605 -vssconverter") out there and found this article.  While not directly what I needed it was enough information to crank up SQL Server Management Studio and start poking around a bit.  So I started with the OwnerId for user that was removed and for the user who was trying to get the code.

 SELECT IdentityId FROM tbl_Identity WHERE (DisplayName LIKE 'ad2\TC30014')  
 SELECT IdentityId FROM tbl_Identity WHERE (DisplayName LIKE 'ad2\RMxxxxx')  

Once I had this I plugged the deleted user's id into the following query to get all the workspaces.

 SELECT TOP 1000 [WorkspaceId]  
    ,[OwnerId]  
    ,[WorkspaceName]  
    ,[Type]  
    ,[Comment]  
    ,[CreationDate]  
    ,[Computer]  
    ,[PolicyOverrideComment]  
    ,[LastAccessDate]  
    ,[CheckInNoteId]  
    ,[DeletionId]  
  FROM [TfsVersionControl].[dbo].[tbl_Workspace]  
  WHERE OwnerId = 276  

This showed me a bunch of workspaces.  What I noticed is that evidently the shelvesets and workspaces are used in a similar model based on the type.  So a little bit of infering and playing in TFS and it looks like if I hack this table, I can reassign all the shelvesets to a valid user (which is sort of the spirit of the article above).

Leaving out some of the details, I ended up with the following query that reassigns the orphaned shelvesets (type=1) from one owner to the other.  Since the WorkspaceName is part of the primary key (and relatively short), I changed the name so that the new owner could distinguish between his shelvesets and those that were reassigned.

  UPDATE [TfsVersionControl].[dbo].[tbl_Workspace]  
  SET OwnerId = 123,  
    WorkspaceName = RIGHT(WorkspaceName + '-Reassigned',64)  
  WHERE OwnerID = 276   
    AND Type = 1  

Looking back at TFS Sidekicks (I verified it first in SSMS - wink) I could see that the more recent shelvesets had indeed been reassigned.  Sucess!!



Now granted, we are on a relatively old version of TFS; so this hack may already be obsolete. But I wanted to put it out here just in case.