When you’re getting the error above is because the user is no longer valid for Reporting Services.
Quick Fix –> Re-create the subscription.
Best Fix –> Reassign the subscriptions to a Service Account.
However, if you have several subscriptions it is not feasible to recreate all of them. Here it is how can you reassign these subscription to someone else.
1. Create a Service Account to assign it as owner of these subscriptions. This is because the Active Directory account set before is no longer valid (the employee left the company.) If you just use another user account and that user left the company you’ll have to do it over and over again. Account created: SA_REPORTS_P
2. Grant DBO permissions on ReportServer database to the new Service Account (SA_REPORTS_P)
3. Grant ALL permissions to the Service Account on Report Server Home directory. Make sure all the folder inherit permissions from parent, if not, individually check the permissions in each folder.
4. Find the OWNERID of the subcriptions that have an invalid user or group name. Run the query below in your ReportServer database and find those with the error.
--Query to get the Last Status of each Subcription and its OwenerID. SELECT s.[SubscriptionID] -- Subscription ID ,s.[OwnerID] -- Report Owner --,s.[Report_OID] -- Report ID , c.Path -- Report Path --,rs.ScheduleID as SQLJobName -- Name of Job on SQL Server ,s.[LastStatus] -- Status of last subscription execution. ,s.[Description] -- Description of the report subscription ,s.[EventType] -- Subscription type ,s.[LastRunTime] -- Last time subscription executed --,s.[Parameters] -- Parameters used for subscription ,s.[DeliveryExtension] -- How to deliver the subscription FROM [ReportServer].[dbo].[Subscriptions] as s left join dbo.Catalog as c on c.ItemID = s.Report_OID left join dbo.ReportSchedule as rs on rs.ReportID = s.Report_OID order by s.LastStatus
5. Identify OwnerID for the Service Account Created (or whatever account you’re going to use). Run the query below to list the users.
SELECT [UserID],[UserName] FROM ReportServer.dbo.Users
UPDATE [Subscriptions] SET [OwnerID] = '96C17DCA-42D6-44AE-B036-9EA1F870AEFD' -- New VALID OwnerID WHERE [OwnerID] = 'EFBCD353-3745-41E4-B2BF-8C23198014AD' -- Old and invalid OwnerID
7. Verify the subscriptions were updated. Run the query below to see how the new user name is not in place.
--Query to get the Report Name and its Subcription Owner. SELECT jobs.name AS JobName, C.path AS ReportPath, C.name AS ReportName, u.username AS SubscriptionOwner FROM dbo.ReportSchedule RS JOIN msdb.dbo.sysjobs jobs ON CONVERT(varchar(36), RS.ScheduleID) = jobs.name INNER JOIN dbo.Subscriptions S ON RS.SubscriptionID = S.SubscriptionID INNER JOIN dbo.Catalog C ON s.report_oid = C.itemid INNER JOIN dbo.users u ON s.ownerid = u.userid order by u.username
The problem should be fixed at this point. Nevertheless, if you want to check if it really works, you can wait for the next subscription to run or invoke it manually. So, here you have a bonus on how to manually call a subscription.
A. In ReportServer, execute the query below and it will give you the SQL Job Name. Two Jobs (subcriptions) that were failing due to an invalid OwnerId are 36F500A6-58DA-4ACB-87AF-9947FEBB5797 and
--Query to get the SQL Job Name of each subscription in the server SELECT jobs.name AS JobName, C.path AS ReportPath, C.name AS ReportName, u.username AS SubscriptionOwner, s.LastStatus FROM dbo.ReportSchedule RS JOIN msdb.dbo.sysjobs jobs ON CONVERT(varchar(36), RS.ScheduleID) = jobs.name INNER JOIN dbo.Subscriptions S ON RS.SubscriptionID = S.SubscriptionID INNER JOIN dbo.Catalog C ON s.report_oid = C.itemid INNER JOIN dbo.users u ON s.ownerid = u.userid order by s.LastStatus
B. Execute the job manually with the script below.
EXEC msdb.dbo.sp_start_job @job_name = '36F500A6-58DA-4ACB-87AF-9947FEBB5797'