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

 

By Phil Steffek

Phil Steffek is a professional sumo wrestler who is hoping to represent the U.S. in the next Olympics. During the off season Phil is a Data Architect working on transactional databases, data warehouse implementations, and all aspects of business intelligence. Also, Phil is not really a sumo wrestler. You can get to know a bit more about my non-sumo doings by checking out my LinkedIn Page

Leave a comment

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