Technical Article

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)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (2)

You rated this post out of 5. Change rating