SSRS Subscriptions stop after a user leaves


The Problem

We have a number of subscriptions configured on our SSRS report server, and we have had 2 users who previously held the responsibility of writing the reports and configuring the subscriptions. When the reports stopped appearing on the relevant file share, or in the recipients mailboxes, a little bit of digging and we found error such as those listed below showing in the subscription properties :-

  • Failure writing file <reportname>: The user or group name ‘DOMAIN\usernomore’ is not recognized.
  • The user or group name ‘DOMAIN\usernomore’ is not recognized

So, as you have probably guessed, the users have now moved on to other organisations, and their AD accounts have been terminated. The interesting thing is there is no way in SSRS that I have found to change the owner property.

The Answer

This fix has been directly plagiarised from http://speeddba.wordpress.com/2008/10/17/failed-sending-mail-the-user-or-group-name-domainuser-is-not-recognized/ . Full credit should go to the author, however as their blog has not been updated for several years, I thought I would pop it in here incase that one gets deleted!

Step 1- Get the Report ID in Question

SELECT ItemID, Name FROM [ReportServer$InstanceName].[dbo].[Catalog] WHERE [Name] =‘MonthlySalesReport’ and [Type] = 2 –Report Item Type

Step 2 – Find the Subscription

SELECT

[SubscriptionID],[OwnerID],[Report_OID] FROM [ReportServer$InstanceName].[dbo].[Subscriptions] WHERE [Report_OID] = ’1551AA5B-0FF1-494B-8725-73E22AA34D9F’

Step 3 – Select another User

SELECT [UserID],[UserName] FROM [ReportServer$InstanceName].[dbo].[Users] WHERE [UserName] = ‘Domain\RSServiceAccount’

Step 4 – Update the Subscription

UPDATE

[ReportServer$InstanceName].[dbo].[Subscriptions] SET [OwnerID] = ’6BDB4964-EE28-4E96-BCB2-E4509FAC909B’ WHERE [OwnerID] = ’3D272CE2-A9B3-4B89-8993-C72E8D43A6EE’ AND [Report_OID] = ’1551AA5B-0FF1-494B-8725-73E22AA34D9F’

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.