Script to Access Subscription Data in SQL Server Reporting Services

9th Dec 2010

This script will find out exactly what reports a particular email address is setup to recieve. Simply run it against your ReportServer database:

DECLARE @EmailAddress NVARCHAR(250)

SET @EmailAddress = 'test@test.com' 

SELECT 
  C.Name
  S.[Description] AS [Description], 
  S.LastRunTime 
FROM 
  Subscriptions S 
  INNER JOIN [Catalog] C ON S.Report_OID = C.ItemID 
WHERE 
  S.ExtensionSettings LIKE '%' + @EmailAddress + '%'

You will then need to manually filter through the results, looking at the description in order to see which report is going to who, and when it was last run. This is because you may have a reply to email address set as the email that you are looking for, which would also be returned by the above query.

I wrote this because it was much easier and quicker than trawling through hundreds of subscriptions. Enjoy.

If anyone has any better ways of manipulating the XML contained within the "ExtensionSettings" field on the Subscription table, then please share.