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’