{"id":628,"date":"2013-11-08T09:27:28","date_gmt":"2013-11-08T16:27:28","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=628"},"modified":"2014-03-03T13:40:55","modified_gmt":"2014-03-03T20:40:55","slug":"preventing-failed-ssrs-subscriptions-which-user-created-your-ssrs-subscriptions","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2013\/11\/preventing-failed-ssrs-subscriptions-which-user-created-your-ssrs-subscriptions\/","title":{"rendered":"Preventing Failed SSRS Subscriptions: Which user created your subscriptions?"},"content":{"rendered":"<p>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)\u00a0SQL Server Reporting Services \u00a0is 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.<\/p>\n<p>[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]<\/p>\n<p>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 <a href=\"http:\/\/www.sqldbpros.com\/2013\/11\/change-the-owner-of-an-ssrs-subscription-the-simple-way\/\">update script to change the owner of the subscription<\/a> to a different user.<\/p>\n<pre>SELECT\r\n    COUNT(1)\r\n   ,u.UserName\r\nFROM\r\n    dbo.ReportSchedule c\r\nJOIN \r\n    dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID\r\nJOIN \r\n    dbo.Users AS u ON d.ownerid = u.UserID\r\nJOIN \r\n    dbo.Catalog e ON itemid = report_oid\r\nGROUP BY\r\n    u.UserName\r\nORDER BY\r\n    1<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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)\u00a0SQL Server Reporting Services \u00a0is keeping track of this information. And when one of those user's account is disabled strange [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,43,136,137,122,58,57],"tags":[35,20,10,60,6,9,12,11,117,59,138,5],"class_list":["post-628","post","type-post","status-publish","format-standard","hentry","category-businss-intelligence","category-microsoft-sql-server","category-proactive","category-reactive","category-ssdt-sql-server-data-tools","category-ssrs-reporting-services","category-t-sql","tag-best-practice","tag-business-intelligence","tag-ms-sql","tag-reporting-services","tag-script","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008","tag-sql-server-2012","tag-ssrs","tag-subscription","tag-tips"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/628","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=628"}],"version-history":[{"count":8,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/628\/revisions"}],"predecessor-version":[{"id":643,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/628\/revisions\/643"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=628"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=628"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=628"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}