SQL Server

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:

Our ReportServer database is actually ReportServer1. Keep that in mind.

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 ;

You can then run this Stored Procedure as follows:

I hope that helps!

Share on Google Plus

Mike250

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

2 Comments

    • Mike
    • September 22, 2017
    • Reply

    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

  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.

Leave a comment

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