Share via

SSRS: How to update the owner of deployed reports and subscriptions

  • Article

There is a known issue for SSRS subscriptions deployed with Active Directory user accounts that are removed (e.g. deleted from the system after the user has left the organization).

To solve this issue, Microsoft suggests adding a functional/service domain account to deploy reports and create subscriptions.

/*------------------------------------------------------------------------------+
| Purpose:  To update the subscription owners
| Note:     SQLCmdMode Script
+--------------------------------------------------------------------------------
*/

:setvar _server "YOUR_SERVER"
:setvar _database "ReportServer"
:connect $(_server) 
USE [$(_database)];
GO

:SETVAR OldUser "DOMAIN\OLDNAME"
:SETVAR NewUser "DOMAIN\NEWNAME"


SET XACT_ABORT ON
BEGIN TRANSACTION

    PRINT '====================================================================='
    PRINT 'Update subscriptions...'
    PRINT '====================================================================='

    ;WITH 
    new_owner
    AS
    (
        SELECT [UserID], [UserName] FROM dbo.[Users] WHERE [UserName] =  N'$(NewUser)'
    )
    , 
    subscription_source
    AS
    (
        SELECT DISTINCT
              s.[Report_OID]
            , [OldOwner] = ou.[UserName]
            , [OldOwnerID] = ou.[UserID]
            , [NewOwner] = nu.[UserName]
            , [NewOwnerID] = nu.[UserID]
        FROM 
            dbo.[Subscriptions] AS s
            INNER JOIN dbo.[Users] AS ou ON ou.[UserID] = s.[OwnerID]
            , new_owner AS nu
        WHERE 
            1=1
            AND ou.[UserName] =  N'$(OldUser)'
    )
    MERGE dbo.[Subscriptions] AS T
    USING subscription_source AS S ON T.[Report_OID] = S.[Report_OID]
    WHEN MATCHED 
    THEN UPDATE SET 
            T.[OwnerID] = S.[NewOwnerID] 
    OUTPUT @@ServerName AS [ServerName], db_name() AS [DatabaseName], $action, inserted.*, deleted.*; 

    PRINT '====================================================================='
    PRINT 'Finished...'
    PRINT '====================================================================='

PRINT '******* ROLLBACK TRANSACTION ******* ';
ROLLBACK TRANSACTION;

--PRINT '******* COMMIT TRANSACTION ******* ';
--COMMIT TRANSACTION;