Post reply

Edit DTS Packages with non admin useres?

  • December 26, 2005 at 5:26 am

    #68057

    Hi all

    I am wondering are there any way to user who is not a sys admin can change a DTS which was made by some other users (like some of sys admins). Can I give some privileges for that?

    Thanks a lot 

  • December 28, 2005 at 1:29 pm

    #611775

    Hi

    We had to implement this just couple of months back because of SOX. As per SOX requirement we had to take away sysadmin rights from many users.

    But some of them needs access to DTS packages and should even be able to modify them sometimes.

    So we have created a stored proc, using which users can change the ownership of the DTS Package and thus be able to modify it. I cannot post the script of the stored procedure here for the obvious reasons, but i can give you enough info for you to be able to do the same.

    The stored proc should be able to update the "owner" field in sysdtspackages table of msdb database. Within the stored proc run all security checks and limit what they should be able to do.

    Give only exec permission on this stored proc to only those users who should be required to modify DTS packages.

    Now users can first run this stored proc and become the owners of the package. Now thay can modify it accordingly and if required change the ownership back to the original (if that makes a difference).

    Hope this works.

  • December 28, 2005 at 3:09 pm

    #611807

    Probably a more supported method would be to create a seperate SQL login that has the necessary privelages to create and edit the DTS packages. Then people changing the DTS packages connect to the server using this "special" login.

     

    --------------------
    Colt 45 - the original point and click interface

Viewing 3 posts - 1 through 2 (of 2 total)

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