iframe-proxy | Sunbelt Computer Software

185 lines (165 loc) · 4.19 KB

/*------------------------------------------------------------------------------+
| Purpose:	How to Update the owner of deployed reports and subscriptions 
| Note:		SQLCmdMode Script
+--------------------------------------------------------------------------------
:setvar _server "Server1"
:setvar _user "***username***"
:setvar _password "***password***"
:setvar _database "ReportServer"
:connect $(_server) -U $(_user) -P $(_password)
USE [$(_database)];
:SETVAR OldUser "DOMAIN\OldUserName"
:SETVAR NewUser "DOMAIN\NewUserName"
SET XACT_ABORT ON
BEGIN TRANSACTION
PRINT '====================================================================='
PRINT 'Find subscriptions for user...'
PRINT '====================================================================='
	SELECT UserID, UserName FROM dbo.Users WHERE UserName =  N'$(OldUser)'
--SELECT 'BEFORE', * FROM user_list
	  'BEFORE'
	, ul.UserName
dbo.Subscriptions sb
INNER JOIN user_list ul ON sb.OwnerID = ul.UserID
PRINT '====================================================================='
PRINT 'Update subscriptions...'
PRINT '====================================================================='
	SELECT Old_UserID = UserID, UserName FROM dbo.Users WHERE UserName =  N'$(OldUser)'
	SELECT New_UserID = UserID, UserName FROM dbo.Users WHERE UserName =  N'$(NewUser)'
--	  uo.UserName 
--	, un.UserName 
	dbo.Subscriptions 
	OwnerID = un.New_UserID 
	dbo.Subscriptions sb
	INNER JOIN user_old uo ON sb.OwnerID = uo.Old_UserID
	, user_new un
PRINT '====================================================================='
PRINT 'Update reports...'
PRINT '====================================================================='
	SELECT Old_UserID = UserID, UserName FROM dbo.Users WHERE UserName =  N'$(OldUser)'
	SELECT New_UserID = UserID, UserName FROM dbo.Users WHERE UserName =  N'$(NewUser)'
	dbo.Catalog 
	CreatedById = un.New_UserID 
	dbo.Catalog AS c
	INNER JOIN user_old uo ON c.CreatedById = uo.Old_UserID
	, user_new un
--WHERE c.Type = 2
	SELECT Old_UserID = UserID, UserName FROM dbo.Users WHERE UserName =  N'$(OldUser)'
	SELECT New_UserID = UserID, UserName FROM dbo.Users WHERE UserName =  N'$(NewUser)'
	dbo.Catalog 
	ModifiedById = un.New_UserID 
	dbo.Catalog AS c
	INNER JOIN user_old uo ON c.ModifiedById = uo.Old_UserID
	, user_new un
--WHERE c.Type = 2
	SELECT Old_UserID = UserID, UserName FROM dbo.Users WHERE UserName =  N'$(OldUser)'
	SELECT New_UserID = UserID, UserName FROM dbo.Users WHERE UserName =  N'$(NewUser)'
	  c.CreatedById
	, c.ModifiedById
	, uo.UserName 
	, un.UserName 
	dbo.Catalog AS c
	INNER JOIN user_old uo ON c.CreatedById = uo.Old_UserID
	, user_new un
PRINT '====================================================================='
PRINT 'Find OLD subscriptions for user...'
PRINT '====================================================================='
	SELECT UserID, UserName FROM dbo.Users WHERE UserName =  N'$(OldUser)'
--SELECT 'BEFORE', * FROM user_list
	, ul.UserName
dbo.Subscriptions sb
INNER JOIN user_list ul ON sb.OwnerID = ul.UserID
PRINT '====================================================================='
PRINT 'Find NEW subscriptions for user...'
PRINT '====================================================================='
	SELECT UserID, UserName FROM dbo.Users WHERE UserName =  N'$(NewUser)'
--SELECT 'BEFORE', * FROM user_list
	, ul.UserName
dbo.Subscriptions sb
INNER JOIN user_list ul ON sb.OwnerID = ul.UserID
ROLLBACK TRANSACTION
--COMMIT TRANSACTION   
PRINT '====================================================================='
PRINT 'Finished...'
PRINT '====================================================================='

Sunbelt Computer Software

PL/B Language Development and Support

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Sunbelt Computer Software

PL/B Language Development and Support

Uh oh!

FilesExpand file tree

SSRS.Update_Subscription_Owner.sql

Latest commit

History

SSRS.Update_Subscription_Owner.sql

File metadata and controls