1

We have a user who is receiving reports from SSRS, but who is not listed in the subscription fields (Find report > Manage > Subscriptions). When I query the report server directly, I see them listed in the CC field. The following is the code I found that shows the user in the CC field. 

DECLARE
     @email VARCHAR(250) = 'user@company.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 [ReportServer].[dbo].[Catalog] AS cat
INNER JOIN [ReportServer].[dbo].[Subscriptions] AS sub ON cat.ItemID = sub.Report_OID
WHERE sub.extensionSettings LIKE '%' + @Email + '%'
ORDER BY cat.[Path], cat.[Name]

We have removed and re-created the subscriptions for this report, but they are still receiving the reports. I have confirmed that the user is not part of any of the groups receiving the reports, and there are no forwards enabled that point to their email. I was not able to find anything through google, as most everything I return talks about not receiving valid subscriptions, or users with forwarding enabled.

I inherited these reports, and I am not super SQL savvy. Is it possible that the emails are hard-coded into the report, or is there another location that contains subscription information I could look into?

S3S
  • 24,809
  • 5
  • 26
  • 45
jangles
  • 11
  • 2
  • When the subscription was removed, did the SQL Agent job that runs the report get removed? Subscriptions create SQL Agent jobs when they get scheduled. See what time the user gets the report and then narrow it down in the `Last Run` column in the SQL Agent Job Activity Monitor. The names of the SQL Agent jobs that SSRS subscriptions create look like GUIDs. (`06A25D77-AE14-4EEF-9E00-D1F13367F3DE`) – SS_DBA Apr 15 '19 at 18:06
  • @WEI_DBA, I can check. The subscriptions that it is using, and was using before, is a shared sub. I will remove the shared and create a unique subscription and check that, so that there will be a unique job name. – jangles Apr 15 '19 at 19:56

1 Answers1

0

I'm guessing, but it may be a data driven subscription where email addresses are loaded from database: Data-Driven Subscriptions

Piotr Palka
  • 3,086
  • 1
  • 9
  • 17
  • I did roll down that thought. However, there is not an option to create a Data-Driven subscription, and none of the subs are marked with the DDS Icon. Also, the Query above returns that the report is not Data-Driven. – jangles Apr 15 '19 at 19:54