Thursday, October 22, 2015

Quick fix: Report Subscription Owner change

When you create report subscriptions using the OpsMgr console, your user account’s guid (assigned by SSRS internally) is made the owner of the subscription. If your user account is removed or disabled, this will cause the report subscriptions to fail. To fix this, you need to replace the owner GUID with a GUID of a valid user. To do so, you can use the following steps:

  1. Using the SQL Server Management Studio, connect to the server hosting the ReportServer database for your OpsMgr environment
  2. Select the ReportServer database, right click and select New Query
  3. Run the following query:

    select OwnerID from subscriptions
    where laststatus like '%Failure%'

    This returns the OwnerID of any reports that have failed to run. You will need this OwnerID for step 5

  4. Open another New Query window, and run the following query:

    select OwnerID from subscriptions
    where laststatus not like '%Failure%'

    This returns the OwnerID of any reports that have not failed to run. You will need this OwnerID for step 5

  5. Open another New Query window, and run the following query:

    USE [ReportServer]
    GO

    UPDATE [dbo].[Subscriptions]
    SET [OwnerID] = <OwnerID of successful reports>
    WHERE OwnerID = <OwnerID of failed reports>
    GO

    It should look something like this:

    USE [ReportServer]
    GO

    UPDATE [dbo].[Subscriptions]
    SET [OwnerID] = '9DF9EBCD-F913-40C1-AEF9-0F7634EAE571'
    WHERE OwnerID = '94738FA7-FD24-4190-9B64-355557DF0035'
    GO

No comments:

Related Posts with Thumbnails