What SSRS Reports Does a User Subscribe to?

If your company uses SQL Server Reporting Services as part of it reporting infrastructure, you’ve probably been asked “What SSRS reports does this user subscribe to?” Often that’s due to a job or role change in the company. Sally is leaving the department and Jonathan is taking over her responsibilities. All of Sally’s reports now need to go to Jonathan.

The first step? Identify all the reports that Sally currently receives via email.

Finding SSRS reports and subscribers

The ReportServer database has a couple of tables that will be helpful here. The aptly named Subscriptions table stores information about the SSRS subscriptions. The Catalog table contains information about the reports.

Joining the tables to get the results is pretty straightforward. The only tricky part is that some of the columns we need to examine are in XML format. (The data type of the column is NTEXT, but the data is in XML.) We need to tease that out using T-SQL.

The following query uses a couple of CTEs to parse the XML. It returns a list of all reports that go to a specific email address. It checks for the specified email address in the TO, CC, and BCC fields. Note: replace the email address in the WHERE clause with one you want to search for.

USE ReportServer; 
GO

WITH
    subscriptionXML
    AS
    (
        SELECT
            s.SubscriptionID ,
            s.Report_OID ,
            s.ExtensionSettings ,
            CONVERT(XML, ExtensionSettings) AS ExtensionSettingsXML ,
            s.Description ,
            s.LastStatus ,
            s.LastRunTime ,
            s.Parameters ,
            c.Name as reportname
        FROM
            ReportServer.dbo.Subscriptions AS s
            JOIN ReportServer.dbo.[Catalog] AS c on s.Report_OID = c.ItemID
    ),
    SubscriptionSettings
    AS
    (
        SELECT
            reportname,
            Description,
            ISNULL(Settings.value('(./*:Name/text())[1]', 'nvarchar(1024)'),
                       'Value') AS SettingName ,
            Settings.value('(./*:Value/text())[1]', 'nvarchar(max)') AS SettingValue,
            LastRunTime,
            LastStatus,
            ExtensionSettings
        FROM
            subscriptionXML
                CROSS APPLY subscriptionXML.ExtensionSettingsXML.nodes('//*:ParameterValue') Queries ( Settings )
    )
SELECT
    *
FROM
    SubscriptionSettings
WHERE
        settingName IN ( 'TO', 'CC', 'BCC' )
    AND settingValue like '%joew@theserogroup.com%' ;

The results are shown below.

(I’ve used this script many times over the years. I originally enhanced a query I found online in a forum or maybe it was StackOverflow. I don’t remember. I’ve long since lost the original reference, otherwise I’d happy give credit where it’s due.)

Want more SQL scripts we find handy?

Check out our Script Library for other SQL queries and dbatools.io commands that we frequently use. Here are a few examples.

• How Much Longer Will CHECKDB Take?
• How to Test SQL Server Backups Using dbatools
• When Was the Last Known Good DBCC CHECKDB Integrity Check?
• 3 Ways to Find Your SQL Server tempdb Data Files

Want to work with The SERO Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. It’s SQL Server Management – Simplified.

Schedule a call and let’s talk.