TFS2010 View and unsubscribe another users alerts

Unfortunately in TFS2010 there is on UI that allows you to view and remove another users post. The issue I had was that a user left, however emails were still being sent from TFS to his account.

The solution - a bit messy.

To view all alerts in a collection:

USE [Tfs_DefaultCollection]

-- Find all subscriptions
SELECT * FROM tbl_EventSubscription ORDER BY Address

*Note that the USE will need to reflect the correct collection database name


Next to unsubscribe. The follow SQL will create a command line to do that :

-- Create script to unsubscribe subscriptions
SELECT 'bissubscribe /unsubscribe /id ' + CAST(id as varchar(255))+ ' /collection http://tfs-server-name:8080/tfs/collection-name'
FROM dbo.tbl_EventSubscription
WHERE Address = 'someuser@mycompany.com'

*Note change tfs-server-name to your TFS server name, and collection-name to reflect the collection you are interested in.


Next, remote onto the TFS server. Open an elevated command line prompt. cd to the TFS installation folder \Tools.
Paste in the result line from the SQL above into the command line prompt and execute (or create a batch file, copy over to TFS server and execute it) .

Now the alert should be removed from the system.