/*------------------------------------------------------------------------------+
| Purpose:	How to Update the owner of deployed reports and subscriptions 
| Note:		SQLCmdMode Script
| Reference: 	http://www.andrewjbillings.com/ssrs-migration-subscriptions-dont-work-if-owner-no-longer-exists/
+--------------------------------------------------------------------------------
:setvar _server "Server1"
:setvar _user "***username***"
:setvar _password "***password***"
:setvar _database "ReportServer"
:connect $(_server) -U $(_user) -P $(_password)
USE [$(_database)];
GO
*/

:SETVAR OldUser "DOMAIN\OldUserName"
:SETVAR NewUser "DOMAIN\NewUserName"

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)'
)
--SELECT * FROM subscription_source
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 '******* ROLLBACK TRANSACTION ******* ';
ROLLBACK TRANSACTION;

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

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