UPDATE: Apparently if SQL Server considers you to be dbo on the msdb database the IF (ISNULL, 0) <> 1) function will return false. It was happening to my users, so instead of using the role that I created (db_ChangeDTSOwner) I used the domain group that the user was in using this notation: “domaindomain_group”.
So the code below would read “IF (ISNULL, 0) <> 1)”. If you do not have a domain, then I would make sure that the user(s) are not part of the dbo group and they remain part of the db_ChangeDTSOwner role in the msdb database.
The simple way to do this in SQL Server 2000 is to add the user or group to the System Administrators Server Role but that breaks one of the major security tenets out there; only give a user the tools to do their job and nothing more. So I had to find another alternative. Here is what I came up with:
- Create a role in the msdb database called db_ChangeDTSOwner and add the users or groups that you want to this role.
Create the following stored procedures:
CREATE PROCEDURE sp_new_reassign_dtspackageowner
name sysname,<br /> @id UNIQUEIDENTIFIER,<br /> @newloginname sysname<br />AS<br /> SET NOCOUNT ON<br /> --// First, is this a valid login?<br /> IF SUSER_SID(newloginname) IS
NULL BEGIN RAISERROR RETURN - Failure
END
-// Does the specified package (uniquely) exist? Referencing by name only may not be unique.
-// We do a bit of a hack here as SQL can’t handle a DISTINCT clause with UNIQUEIDENTIFIER.
-//
id will get the first id returned; if only name specified, see if there are more.<br /> DECLARE @findid UNIQUEIDENTIFIER<br /> SELECT @findid = id FROM sysdtspackages<br /> WHERE (name IS
NOT NULL OR
id IS NOT NULL)<br /> AND (name IS
NULL OR
name = name)<br /> AND (id IS
NULL OR
id = id)<br /> IF @rowcount = 0
BEGIN DECLARE pkgnotfound NVARCHAR(200)<br /> DECLARE @dts_package_res NVARCHAR(100)<br /> SELECT @pkgnotfound = FORMATMESSAGE(14599) + ' = ''' + ISNULL(name,
FORMATMESSAGE) + ‘’’; ’ +
FORMATMESSAGE + ’ {’
SELECT pkgnotfound = @pkgnotfound + CASE WHEN @id IS NULL THEN FORMATMESSAGE(14589) ELSE CONVERT(NVARCHAR(50), @id) END + '}.{'<br /> SELECT @pkgnotfound = @pkgnotfound + FORMATMESSAGE(14589) + '}'<br /> SELECT @dts_package_res = FORMATMESSAGE(14594)<br /> RAISERROR(14262, 16, 1, @dts_package_res, @pkgnotfound)<br /> RETURN(1) -- Failure<br /> END ELSE IF @name IS NOT NULL AND @id IS NULL AND<br /> EXISTS (SELECT * FROM sysdtspackages WHERE name = @name AND id <> @findid)<br /> BEGIN<br /> RAISERROR(14595, -1, -1, @name)<br /> RETURN(1) -- Failure<br /> END<br /> SELECT @id = @findid<br /> --// Only the owner of DTS Package ''%s'' or a member of the sysadmin role may reassign its ownership.<br /> --// sp_add_dtspackage ensures that all versions have the same owner_sid.<br /> IF (ISNULL(IS_MEMBER(N'db_ChangeDTSOwner'), 0) <> 1)<br /> BEGIN<br /> IF (NOT EXISTS (SELECT * FROM sysdtspackages WHERE id = @id AND owner_sid = SUSER_SID()))<br /> BEGIN<br /> SELECT @name = name FROM sysdtspackages WHERE id = @id<br /> RAISERROR (14585, -1, -1, @name)<br /> RETURN(1) -- Failure<br /> END<br /> END<br /> --// Everything checks out, so reassign the owner.<br /> --// Note that @newloginname may be a sql server login rather than a network user,<br /> --// which is not quite the same as when a package is created.<br /> UPDATE sysdtspackages<br /> SET owner_sid = SUSER_SID(newloginname),
owner =
newloginname<br /> WHERE id = @id<br /> RETURN 0 -- SUCCESS</div><br />The above code is an altered Microsoft stored procedure. Instead of checking to see if the user is in the System Administrators Server Role is checks to see if the user or group is in the db_ChangeDTSOwner role. <br /><br /> <div class="codeSample">CREATE PROCEDURE proc_DTSChangeOwner<br />package_name sysname,
old_owner sysname,<br />new_owner sysname
AS
DECLARE id uniqueidentifier, @name sysname, @error INT<br />/**<br />Author: Carlos Rodriguez<br />Date Created: 1-13-2004<br />Summary: Changes owner of DTS package so that edits can be made to it<br />ChangeLog:<br />----------------------------------------------------------------------<br />Date: 1-14-2003 <br />Author: Carlos Rodriguez<br />Change: Added logging to a table<br />----------------------------------------------------------------------<br /><br />**/<br /><br />/**<br /><br />If the package name or the old owner don't match up, display error message and stop procedure<br /><br />**/<br />IF (NOT EXISTS (SELECT * FROM msdb..sysdtspackages WHERE [owner] = @old_owner AND [name] = @package_name))<br />BEGIN<br /> RAISERROR('Package ''%s'' does not exist or owner name is wrong', 16, 1, @package_name)<br /> RETURN<br />END<br /><br />--First, is this a valid login?<br /> IF SUSER_SID(new_owner) IS
NULL BEGIN RAISERROR RETURN - Failure
END
SET name = @package_name<br />/**<br />For each instance of a package name in the msdb..sysdtspackages table with the old owner name, let's <br />change that to the new owner name. In the msdb..sysdtspackages table, there can be multiple versions<br />of the same package so we need to do this.<br />**/<br />DECLARE cur_sysdtspackages CURSOR FOR <br /> SELECT DISTINCT [name], [id]<br /> FROM sysdtspackages <br /> WHERE [owner] = @old_owner AND [name] = @package_name<br /> OPEN cur_sysdtspackages<br /> FETCH NEXT FROM cur_sysdtspackages<br /> INTO @name, @id<br /> WHILE @FETCH_STATUS = 0
BEGIN
-Call undocumented stored procedure to change the owner of the package
EXEC sp_new_reassign_dtspackageowner
name=name,
id=id,
newloginname=new_owner
FETCH NEXT FROM cur_sysdtspackages
INTO name, @id<br /> END<br /> CLOSE cur_sysdtspackages<br /> DEALLOCATE cur_sysdtspackages<br />--Add a record to the log (DTSChangeOwnerLog table)<br />INSERT INTO DTSChangeOwnerLog<br />VALUES(name, @old_owner, @new_owner, getdate())
GO
And finally create this table for logging:
CREATE TABLE [DTSChangeOwnerLog] (
[record_id] [int] IDENTITY (1, 1) NOT NULL ,
[package_name] [varchar] (100) NULL ,
[old_owner] [varchar] (30) NULL ,
[new_owner] [varchar] (30) NULL ,
[dt] [datetime] NULL ,
PRIMARY KEY NONCLUSTERED
(
[record_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Remember to give the user or group execute permissions on the new stored procedures. A user can change the owner
DTS package by running this code from the msdb database:
EXEC proc_DTSChangeOwner ‘name of DTS package’, ‘domainolduser’, ‘domain
ewuser’.
