Find SSRS subscriptions of an email address

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!

Mike250

Australian. Sport. Passionate Cricket Fan. Go Pro. Abseiling. Snorkeling. Travel. Golf R. SQL Server Developer. Three sons. One daughter. Last Trip: New York.

Related Posts
16 Comments
    • Mike
    • On: September 22, 2017

    This just saved me a ton of time

    I modified your code to also allow me to replace an OldEmail with a new one

    ALTER PROCEDURE [SSRS_Subscription_Email_ReadorRemoveSp]
    — Input Parameters
    @OldEMail VARCHAR(250),
    @NewEmail VARCHAR(250),
    @Action VARCHAR(20) = ‘Read’, — Read, Remove
    @Infobar InfobarType OUTPUT
    AS
    BEGIN

    IF @Action = ‘Replace’ AND @NewEmail IS NULL
    BEGIN

    SET @Infobar = ‘If the Process is to REPLACE, the New Email Address cannot be NULL’

    RETURN 16

    END

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

    SET NOCOUNT ON;

    DECLARE @replaceemail VARCHAR(250), @rowcount INT

    — The report

    IF @Action IN (‘Read’,’Remove’,’Replace’)
    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 [ReportServer].[dbo].[Catalog] AS cat
    INNER JOIN [ReportServer].[dbo].[Subscriptions] AS sub ON cat.ItemID =
    sub.Report_OID
    WHERE sub.extensionSettings LIKE ‘%’ + @OldEMail + ‘%’
    ORDER BY cat.[Path], cat.[Name]
    END

    IF @Action = ‘Remove’
    BEGIN

    — Add a ; to the email for replace

    SET @replaceemail = @OldEMail + ‘;’

    UPDATE [ReportServer].[dbo].[Subscriptions]
    SET extensionSettings = REPLACE(REPLACE(CONVERT(VARCHAR(MAX),
    extensionSettings),@replaceemail,@OldEMail),@OldEMail,”)
    WHERE extensionSettings LIKE ‘%’ + @OldEMail + ‘%’

    SELECT @rowcount = @@ROWCOUNT
    SET @Infobar = @OldEMail + ‘ has been removed from ‘ +
    CONVERT(VARCHAR(10),@rowcount) + ‘ subscription records.’

    RETURN 0

    END

    IF @Action = ‘Replace’
    BEGIN

    — Add a ; to the email for replace

    UPDATE [ReportServer].[dbo].[Subscriptions]
    SET extensionSettings = REPLACE(CONVERT(VARCHAR(MAX),
    extensionSettings),@OldEMail,@NewEmail)
    WHERE extensionSettings LIKE ‘%’ + @OldEMail + ‘%’

    SELECT @rowcount = @@ROWCOUNT
    SET @Infobar = @OldEMail + ‘ has been replaced in ‘ +
    CONVERT(VARCHAR(10),@rowcount) + ‘ subscription records with ‘ + @NewEmail + ‘.’

    RETURN 0

    END

    RETURN 0

    END

    Reply
  1. Really glad to hear that helped mate, we’ve been using this now in our BI Production systems for a few years. Really enjoy your addition, it’s funny that you should add this because we ended up doing similar as well as extending again to add an email address to every subscription another is a part of. Think “new Finance person” who needs to receive exactly the same reports as an existing Finance person. Saves so much time! Thanks for stopping by, Mike.

    Reply
      • Shwetha
      • On: March 28, 2018

      Hi Mike
      This really saved my time. Thank you for sharing this code. Will it possible for you share your code to add an email to existing subscriptions?
      Thank you in advance

      Reply
      • My pleasure mate. Sure, I will mail it to you!
        Michael

        Reply
    • Wessies
    • On: June 1, 2018

    Hi Michael,
    Would it be possible to share the code to add an email to existing subscriptions?
    Thanks!!!

    Reply
    • I’m on it, even after all this time!

      Reply
  2. Hi Mike,

    So glad that you posted this script and that there was a followup to even improve an already great piece of code. You came through in the clutch. Thanks.

    Reply
    • Absolute pleasure, George!

      Reply
    • Brandon
    • On: January 15, 2019

    Hello Mike,
    This is very helpful and saves a ton of time! Do you mind sharing your extended code to add users to existing subscription?

    Reply
    • You bet mate, have had a few requests. I’ll look for it and post!

      Reply
    • Mike
    • On: August 15, 2019

    Mike…Great content. Our reporting team finally grew tired of manually maintaining subscriptions and asked me to write a stored procedures to do perform this task. I seldom write code without first searching for someone smarter than me that has already written it. 🙂 I’m sure you’ve tweaked this script through the years. Would you be willing to mail me your current versions for all three scenarios? Adding, Replacing and Removing? Thank so much.

    Reply
    • Mike Freeney
    • On: August 17, 2019

    Mike, This is great content, thank you very much. Our reporting team has finally grown tired of manually managing the report distributions, and asked me to write a stored procedure to do exactly this task. Instead of coding, I first looked for someone smarter and faster than me who’s already done it. Glad I found you :). I’m sure you’ve tweaked this through the years. Would you mind mailing me all 3 operations of your current code…Adding, Removing, and Replacing? Cheers

    Reply
    • Unfortunately, we’re predominantly now using Power BI! I’ll have to dig in and see what I can find. Apologies for the lateness of my reply.

      Reply
    • micmor
    • On: March 11, 2020

    This stored procedure is super handy, and should be added to Employee termination procedures.

    While testing the procedure, I noticed two issues not addressed:

    1) legacy SSRS report subscriptions created years ago tend to have subscription descriptions that list email addresses added to the subscription — apparently SSRS used to update the description automatically as new subscriber email addresses were added in the UI.

    I appended this sql to the Remove section:

    SET @replaceemail = @email + ‘,’ — different delimiter in the description column

    UPDATE [ReportServer].[dbo].[Subscriptions]
    SET Description = REPLACE(REPLACE(CONVERT(VARCHAR(MAX),Description),@replaceemail,@email),@email,”)
    WHERE description like ‘%’ + @Email + ‘%’

    SELECT @rowcount = @@ROWCOUNT
    SELECT @email + ‘ has been removed from ‘ + CONVERT(VARCHAR(10),@rowcount) + ‘ subscription descriptions.’

    2) It is possible that @email could be the only subscriber, leaving a report subscription with no email address: extensionsettings column contains ”.
    [Not the end of the world, but not great either, eh?]

    I appended this sql to the Remove section:
    ——————————————————————————
    — There may be some subscriptions modified in which
    — the user’s @email was the only subscriber
    ——————————————————————————
    — show these report subscriptions
    — and disable report (for the time being)

    select s.*
    from [ReportServer].[dbo].[Subscriptions] s
    where s.ExtensionSettings like ‘%%’

    –select count(*) from [ReportServer].[dbo].[Subscriptions]
    –where InactiveFlags = 1

    update [ReportServer].[dbo].[Subscriptions]
    set InactiveFlags = 1
    where ExtensionSettings like ‘%%’

    –select count(*)
    –from [ReportServer].[dbo].[Subscriptions]
    –where InactiveFlags = 1

    SELECT @rowcount = @@ROWCOUNT
    SELECT CONVERT(VARCHAR(10),@rowcount) + ‘ subscriptions inactivated where ‘ + @email + ‘ was the only subscriber.’

    —————————————————————————
    —————————————————————————

    Reply
    • This is great Micmor, thank you. Very much appreciate your contribution!

      Reply
    • Corbett Enders
    • On: October 19, 2021

    That extra code to inactivate subscriptions where the user was the only subscriber… doesn’t work. It just inactivated all of my subscriptions lol.

    Reply
Leave a comment

Your email address will not be published. Required fields are marked *