How to Give a User(s) Permission to Change the Owner of a DTS Package
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 PROCEDURE sp_new_reassign_dtspackageowner
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.
-//
BEGIN
DECLARE
SELECT
owner =
AS
DECLARE
BEGIN
RAISERROR
RETURN- Failure
END
SET
BEGIN
-Call undocumented stored procedure to change the owner of the package
EXEC sp_new_reassign_dtspackageowner
FETCH NEXT FROM cur_sysdtspackages
INTO
GO
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 NULLBEGIN
RAISERROR
RETURN
END
-
-
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 = 0BEGIN
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 sysnameAS
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 NULLBEGIN
RAISERROR
RETURN
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 = 0BEGIN
EXEC sp_new_reassign_dtspackageowner
name=name, id=id, newloginname=new_ownerFETCH 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
[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’.
ewuser’.
