The identity trinity: tbl_Changeset, tbl_VCIdentityMap and Constants
First of all, please note that this post applies to TFS2013. Earlier or later versions may have a different structure, leaving this deprecated or invalid for you.
Now, I’m posting this just out of awareness, because I had to look around a lot before I figured out how some of the tables of the project collection database are related. Usually we’d see these kinds of relationships denoted by foreign keys and whatnot, and I don’t exactly know the reason this isn’t the case here. Must’ve a motive.
Not many people will find use in this, but it may be very important if you’re customizing TFS or migrating it. Or just for any shadowy reason you might have.
Either way, I’ll be straight forward:
Though there are no foreign keys to depict that, the OwnerId and CommitterId fields from the tbl_changeSet table are connected to IdentityId from the tbl_VCIdentityMap table. To get the DisplayName and such, you have to join TeamFoundationId from tbl_VCIdentityMap table to its equivalent from Constants table.
Also, be sure to include PartitionId in the join clause. Note that this doesn’t just apply to the tbl_changeSet table, and may be useful elsewhere.
I figured this out by checking some of the stored procedures in the tfs database, like prc_QueryChangeSet. They are encrypted but it’s no big deal, you can check them out. There are lots of sql scripts around the web to do this. To make things easy, there are tools that can decrypt it to you, such as ApexSQL Complete, or Optillect SQL Decryptor.
Own it up and don’t committer suicide!
If you noticed, there are two possible ids that can lead to an identity. OwnerId and CommitterId. Sorry for the terrible pun!
OwnerId points to the person responsible for the content of the changeset. CommitterId points to the one who checked it in. They are USUALLY the same person, though this may not be the case. Team Foundation Server allows executing operation on behalf of other users, so the committer could be someone else. Having two fields allows this kind of functionality so you always know who clicked the button, and whose workspace was being sent to the glorious fields of source control history.
Generally speaking you’ll at most times want OwnerId, not CommitterId. I’ve mistakenly assigned work items to a service account before because I didn’t know that. An automated bot would check code from other people in, create a work item and erroneously assign it to itself.
Why do I need this knowledge?
I must say… As you delve deep inside the guts of TFS customization, this kind of knowledge can be useful for optimizations, specific queries to figure out specific issues, and I’d even say reports. But don’t use the tfs project collection database directly for that last one. If possible use/customize the cube and move from there.
Either way it’s good to know how things work. By inspecting the code and database I’ve come across a lot of useful answers to the most troublesome questions. I hope I’ll be able to clarify some of them on these posts.
At last but not least, if you’re reading this page, thank you.
I’m very open to comments, questions, and critiques. Expect me to answer to all of them.
5 thoughts on “Your identity just got OWNED, or “How to figure out OwnerId, CommitterId, and why isn’t the committer always the owner””
My tfs is 2015, I have someone left a data set for a report using the query below
select COUNT(*) AS [TFVC Users] from [Tfs_Configuration].[dbo].tbl_Identity as ic JOIN tbl_VCIdentityMap as i ON i.TeamFoundationId=ic.id where ic.isgroup = 0
when I went to database and couldn’t locate table tbl_VCIdentityMap , where I can find ?
LikeLiked by 1 person
I particularly cannot give you an accurate answer right now because I’m still using TFS 2013, but from what I’ve heard, there are lots of changes in the schema, so this post may be outdated for you. I’ll try to install tfs 2015 trial on a virtual machine and test it out for you. If it works, I might as well create a separate post for TFS 2015
Any update? we just upgraded from TFS2013 to TFS2015 and the tbl_VCIdentityMap table nolonger exists as well. We were fetching data from there to know all changes since a specific changeset.
same problem here as well. Any update?
Oh, yes. I should’ve answered the other comment. They’ve changed the OwnerId and CommiterId data types to UNIQUEIDENTIFIER (guid), so you don’t have to use the middleman anymore. Additionally, I think it may be better to use the identity table from the Configuration database instead, joining on the Id column. (And by that I mean this one: [Tfs_Configuration].[dbo].tbl_Identity)