DTS Permissions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

DTS Permissions

Author

Topic

rme8494
Yak Posting Veteran

98 Posts

Posted - 2003-12-14 : 19:23:30

All,
I have a permissions question for you. Is it true that only an owner or an administrator can edit/change the properties of a DTS package? If so is there a way to change an owner of the DTS package?

The issue is we had a developer (who was not an admin) that left and we need to give the new developer access to change the DTS packages. I would prefer not to give the new developer admin rights.

Thanks
Ryan

Ryan Everhart
SBC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-15 : 15:03:10

DTS packages are stored in msdb.dbo.sysdtspackages. This table has an owner column. Perhaps, changing this will fix your problem. Modifying system tables is not recommended, so backup the database prior to making any changes. You might want to consider doing this on a test system first.

Tara

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2003-12-16 : 01:41:38

quote:
Originally posted by rme8494

All,
I have a permissions question for you. Is it true that only an owner or an administrator can edit/change the properties of a DTS package? If so is there a way to change an owner of the DTS package?

The issue is we had a developer (who was not an admin) that left and we need to give the new developer access to change the DTS packages. I would prefer not to give the new developer admin rights.

Check this site for more info on DTS ownership issues and workarounds:
http://www.sqldts.com/default.aspx?212

There is a workaround mentioned that removes the sysadmin and/or owner check of a DTS package. Here is what you do.

btw, this is totally unsupported by microsoft and you should only make this modification on a development box.

1. Script the sp_add_dtspackage sproc in the msdb database to a file.
2. Edit the code and search for the block of code below.
3. Remark out the following lines as shown below (highlighted in red):

  --// We will use the original owner_sid for all new versions - all must have the same owner.
  --// New packages will get the current login's SID as owner_sid.
  DECLARE @owner_sid VARBINARY(85)
  SELECT @owner_sid = MIN(owner_sid) FROM sysdtspackages WHERE id = @id
  IF @@rowcount = 0 OR @owner_sid IS NULL
  BEGIN
    SELECT @owner_sid = SUSER_SID()
  END /*  ELSE BEGIN
    --// Only the owner of DTS Package ''%s'' or a member of the sysadmin role may create new versions of it.
    IF (@owner_sid <> SUSER_SID() AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1))
    BEGIN
      RAISERROR (14586, -1, -1, @name)
      RETURN(1) -- Failure
    END
  END
  */

4. Drop and recreate the procedure using your new code.

A similar change can also be made to sp_drop_dtspackage.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources