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