For any of you that work closely with Reporting Services, I am sure you can vouch that the system offers a great amount of flexibility and capability. At the same time though you also have probably encountered your fair share of strange, random and undocumented issues. One today I found happened with a Subscription.
Failed sending mail: The user or group name ‘Domain\UserNoMore’ is not recognized
Recently, we had a user leave the company. For the sake of this article we will happily refer to him as UserNoMore. When you create a subscription you are not propted to define an owner. You get the happy assignement automagically. When the user account is deactivated authentication ceases and emails and file archives begin to fail.
In my experience here it took a little finess and hackery to correct. Now let me first say I niether condone nore dismiss the need of making direct changes to data. So in this case after attempting any known fix or finding any interface I determined the fix would be in a bit of DML. Really the fix was pretty simple. Find the subscription, determine a different user to own, update the subscriptions. In this case I selected the service account for Reporting Services so I wouldn’t have to worry later (and I guess technically you could do this for all subscriptions if thats how you shop is set security wise).
In the ReportServer$InstanceName database for reporting services you will find 3 key tables:
Catalog – Reports, Folders and basically all Items
Subscriptions – The scheduled report processes
User – Well…any user or group in Reporting Services
Step 1- Get the Report ID in Question
SELECT [ItemID], [Name]
WHERE [Name] ='MonthlySalesReport'
and [Type] = 2 --Report Item Type
Step 2 – Find the Subscription
WHERE [Report_OID] = '1551AA5B-0FF1-494B-8725-73E22AA34D9F'
Step 3 – Select another User
WHERE [UserName] = 'Domain\RSServiceAccount'
Step 4 – Update the Subscription
SET [OwnerID] = '6BDB4964-EE28-4E96-BCB2-E4509FAC909B'
WHERE [OwnerID] = '3D272CE2-A9B3-4B89-8993-C72E8D43A6EE'
AND [Report_OID] = '1551AA5B-0FF1-494B-8725-73E22AA34D9F'
Really an easy fix…not clean but it worked for me. Feel free to provide feedback.