The following is for TFS 2013 Update 5
** WARNING ** Getting caught editing the TFS database directly
** will void your Microsoft Support Agreement. **
What follows is not for the uninitiated. ** Proceed at your own risk. **
Locate the user or users with email addresses needing to be set. There can be duplicates in the Identities table. I found that the ones with the highest SequenceId were the active Identities.
Use Tfs_TFSConfiguration
SELECT i1.AccountName, i1.Id FROM tbl_Identity AS i1
LEFT OUTER JOIN tbl_Identity AS i2
ON (i1.AccountName=i2.AccountName AND i1.SequenceId<i2.SequenceId)
WHERE i2.AccountName IS NULL
AND i1.AccountName in ('<your first user>','<another user>','<and so on>')
This gives a list of the most recent the Id(s), in GUID form, for the accounts that you need to update. These GUIDs must be reformatted into ArtifactId(s), which is a transformed binary format. This is accomplished by reversing the byte order (low to high) or each of the first three parts of the GUID, but leaving the last two parts in order. E.g.:
Returned 'Id' GUID =01020304-0506-0708-090A-0B0C0D0E0F10
Byte Swapped GUID =04030201-0605-0807-090A-0B0C0D0E0F10
Reformatted 'ArtifacId'=0x0403020106050807090A0B0C0D0E0F10
Next, you have to find the PropertyId(s) used by TFS for email notifications. In TFS 2013 U5, this can be found with the following query:
USE Tfs_TFSConfiguration
SELECT Name, PropertyId FROM tbl_PropertyDefinition WHERE Name LIKE '%Address%'
This will give you the PropertyId(s) for ConfirmedNotificationAddress and CustomNotificationAddresses; which are the two property fields used by TFS 2013 U5 to send notification emails.
Next, you have to find the InternalKindId for the Identity Framework for the TFS DatabaseCategory
USE Tfs_TFSConfiguration
SELECT Description, InternalKindId FROM tbl_PropertyArtifactKind
WHERE Description='Identity'
Now to put it all together, ...
If the configuration records for your user(s) already exist, you can update the settings with:
USE Tfs_TFSConfiguration
UPDATE tbl_PropertyValue SET LeadingStringValue='<user's notification email address>'
WHERE ArtifactId=<ArtifactId, reformatted from tbl_Identity query>
AND PropertyId IN ('<first PropertyId from tbl_PropertyDefinition>', '<second id>')
Note: that ArtifactId is a binary value, based upon a semi-byte-swapped database GUID, and will not match a quoted value in the UPDATE query. I.e. this part of the query will look something like:
WHERE ArtifactId=0x90D490F6BF7B31491CB894323F38A91F AND
Below I assume that the PartitionId is '1'; this should be verified before you continue by a brief scan of the records in the tbl_PropertyValue table.
If you are loading configuration settings that have not yet been set:
USE Tfs_TFSConfiguration
INSERT INTO tbl_PropertyValue
(PartitionId, ArtifactId, InternalKindId, Version, PropertyId, LeadingStringValue)
VALUES ('1', <ArtifactId, reformatted from tbl_Identity query>,
'<InternalKindId from tbl_PropertyArtifactKind>',
'0',
'<first PropertyId from tbl_PropertyDefinition>',
'<user's notification email address>'),
('1', <ArtifactId, reformatted from tbl_Identity query>,
'<InternalKindId from tbl_PropertyArtifactKind>',
'0',
'<second PropertyId from tbl_PropertyDefinition>',
'<user's notification email address>')
Note: that ArtifactId must be an unquoted binary value, transformed from the GUID returned from the tbl_Identity as explained above.
Note: that two records are created for each ArtifactId, one for each PropertyId.
** WARNING ** Getting caught editing the TFS database directly
** will void your Microsoft Support Agreement. **
** Proceed at your own risk. **
(This works for me, ... but, I do not have a Microsoft Support Agreement to invalidate.)
