Find SSRS subscriptions of an email address

I recently had the need to remove an email address from all SSRS subscriptions as a certain person was no longer with the company. Rather than weed through Report Manager or the emails you yourself receive (you do receive every report yourself, right?), you can run the following against ReportServer:

DECLARE
	@email VARCHAR(250) = 'jane@janedoe.com'

SELECT 
  cat.[Path],
  cat.[Name],
  CASE 
    WHEN sub.Description LIKE '%@%' THEN 0 
    ELSE 1 
  END AS DDS,
  CASE 
    WHEN sub.Description LIKE '%@%' THEN '' 
    ELSE sub.Description 
  END AS DDSDescription,
  sub.SubscriptionID,
  sub.ExtensionSettings
FROM [ReportServer1].[dbo].[Catalog] cat
INNER JOIN [ReportServer1].[dbo].[Subscriptions] sub 
  ON cat.ItemID = sub.Report_OID
WHERE sub.extensionSettings LIKE '%' + @Email + '%'
ORDER BY cat.[Path], cat.[Name]

I decided I wanted to take this a step further and remove this user from all subscriptions in one fell swoop, rather than manually configuring each and every subscription. The following Stored Procedure will allow you to run a read only or remove based on the email address passed. It takes into consideration that the email address may or may not have a following semi-colon ;

CREATE PROCEDURE [report].[SSRS_Subscription_Email]

 -- Input Parameters
 @email VARCHAR(250),
 @action VARCHAR(20) = 'Read'	-- Read, Remove

AS
BEGIN

 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.

 SET NOCOUNT ON;

 -- The report

 IF @action IN ('Read','Remove')
  BEGIN
   SELECT 
	cat.[Path],
	cat.[Name],
	CASE WHEN sub.Description LIKE '%@%' THEN 0 ELSE 1 END AS DDS,
	CASE WHEN sub.Description LIKE '%@%' THEN '' ELSE sub.Description END AS DDSDescription,
	sub.SubscriptionID,
	sub.ExtensionSettings
  FROM [ReportServer1].[dbo].[Catalog] AS cat
  JOIN [ReportServer1].[dbo].[Subscriptions] AS sub ON cat.ItemID = sub.Report_OID
  WHERE sub.extensionSettings LIKE '%' + @Email + '%'
  ORDER BY cat.[Path], cat.[Name]
 END

 IF @action = 'Remove' 
  BEGIN
   -- Add a ; to the email for replace
   DECLARE @replaceemail VARCHAR(250), @rowcount INT
   SET @replaceemail = @email + ';'

  UPDATE [ReportServer1].[dbo].[Subscriptions]
  SET extensionSettings = REPLACE(REPLACE(CONVERT(VARCHAR(MAX),extensionSettings),@replaceemail,@email),@email,'')  
  WHERE extensionSettings LIKE '%' + @Email + '%'

  SELECT @rowcount = @@ROWCOUNT
  SELECT @email + ' has been removed from ' + 
   CONVERT(VARCHAR(10),@rowcount) + ' subscription records.'

 END

END

You can then run this Stored Procedure as follows:

-- Read the subscriptions this person belongs to
EXEC report.SSRS_Subscription_Email 'jane@janedoe.com', 'Read'

-- Remove the subscriptions this person belongs to
EXEC report.SSRS_Subscription_Email 'jane@janedoe.com', 'Remove'

I hope this helps!