Change DTS package owner

,

This simple script is useful if you have a lot of scripts owned by developers, and you want to re-assign them all in one go to SA or any other login. Note the section which produces a rollback if you have any problems after the re-assign

DECLARE @DTSPKG CURSOR
DECLARE @name varchar (50)
DECLARE @id varchar (100)
DECLARE @DTS varchar (30)
DECLARE @Statement nvarchar(300)
DECLARE @Owner varchar (30)

DECLARE DTSPKG CURSOR FOR
SELECT DISTINCT [name],[id],[owner] FROM sysdtspackages

OPEN DTSPKG

FETCH NEXT FROM DTSPKG
INTO @name, @id, @owner
	WHILE @@FETCH_STATUS = 0

	BEGIN
	--To allow for the easy rollback, use the statements generated
	PRINT N'EXEC sp_reassign_dtspackageowner ' + char(39) + @name + char(39) + ', ' + char(39) + @id+ char(39) + ', ' + char(39) + @owner + char(39)
		SET @Statement = 'EXEC sp_reassign_dtspackageowner ' + char(39) + @name + char(39) + ', ' + char(39) + @id+ char(39) + ', ' + 'sa'
		EXEC sp_executesql @Statement
			FETCH NEXT FROM DTSPKG
  			INTO @name, @id, @owner
	END

CLOSE DTSPKG
DEALLOCATE DTSPKG
GO

Rate

4 (2)

Share

Share

Rate

4 (2)