Preventing Failed SSRS Subscriptions: Which user created your subscriptions?


Check it out my friends. You need to keep an eye on which accounts are creating your SSRS subscriptions. Why? Because buried inside the ReportServer database (with no way to change it from the front end) SQL Server Reporting Services  is keeping track of this information. And when one of those user’s account is disabled strange things happen. Data driven subscriptions get stuck in a pending status. Emails don’t go out. Subscriptions fail with strange errors.

[box type=”tick” size=”large”]Take Action: Use the script below to confirm all your subscriptions are linked to service accounts not individual users.[/box]

Time to get proactive! Run the following query to find who the “owner” of a subscription is and see if you’re at risk. If you find something suspicious then you can use our update script to change the owner of the subscription to a different user.

SELECT
    COUNT(1)
   ,u.UserName
FROM
    dbo.ReportSchedule c
JOIN 
    dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID
JOIN 
    dbo.Users AS u ON d.ownerid = u.UserID
JOIN 
    dbo.Catalog e ON itemid = report_oid
GROUP BY
    u.UserName
ORDER BY
    1

 


Leave a Reply

Your email address will not be published.