Permissions required to edit DTS package

  • speja01

    SSC Eights!

    Points: 984

    Hello Everyone,

    I have developers working in a development environment creating and saving DTS packages. Due to project allocation, there is often the need for one developer to edit a DTS package created by a different developer. As a security measure, we never allow anyone outside of our DBA's to reside in the sysadmin role. How can a developer who is neither in the sysadmin role or the owner of the DTS package edit that package? There has to be another workaround besides adding the developer to the sysadmin role.

    Thanks

  • Carl Federl

    One Orange Chip

    Points: 25384

    Stored procedure msdb.dbo.sp_reassign_dtspackageowner can be used to change the owner of a DTS package but MS has hard-code logic to restrict the usage only to the current package owner or a sysadmin .

    Suggest creating a new procedure based on sp_reassign_dtspackageowner but that does not include the hard-coded security logic and then grant execute rights appropriately.

    These are the SQL statements that need to be excluded:

    IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)

    BEGIN

    IF (NOT EXISTS (SELECT * FROM sysdtspackages WHERE id = @id AND owner_sid = SUSER_SID()))

    BEGIN

    SELECT @name = name FROM sysdtspackages WHERE id = @id

    RAISERROR (14585, -1, -1, @name)

    RETURN(1) -- Failure

    END

    END

    SQL = Scarcely Qualifies as a Language

  • Greg Charles

    SSC-Forever

    Points: 45403

    If the packages are saved with an owner password set, anyone who knows the password can edit, save, and run the package. See "Handling Package Security in DTS" in BOL.

    This is how we share maintenance of packages among a group of people.

    Greg

  • speja01

    SSC Eights!

    Points: 984

    Thanks for the suggestion! I copied the sp and made the necessary security adjustments removing the check for ownership and sysadmin membership. I added a security check to verify the user was a member of db_dtsadmin just for some sort of security handling.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply