Post reply

DTS Security

  • January 13, 2004 at 4:28 pm

    #160396

    In our production environment, I am looking into tightening the security for DTS packages and limit access to edit and execute the DTS packages to DBA/sysadmins only, while granting developers view access and no access to consultants. At present there are DTS packages owned by developers. As such they have execute and edit permissions on packages that they own.

    Here is my solution:

    1). I can create a windows account and set the ownership of all existing DTS packages to that account, so nobody (other than sysadmin role members) can edit or execute them. Even developers who owned the packages before, cannot edit them now..

    2). Remove the guest account in the msdb database, revoke execute permissions on the sp_add_dtspackage procedure. Then add developers to that database with public membership. This would let the developers read / execute the DTS packages. (Is there any way I can grant read but not the execute permissions? If not I can live with that.).

    3).Since consulatants are neither added to the msdb explicitly nor do they have access thru guest account, they should not have any access to the DTS packages.

    Is this a reasonable approach? Or are there any better alternatives?

    Just curious, Is there any reason/explanation why DTS packages are saved under the windows account rather than the sqlaccount that created them using the SQL authentication. It does so even when the windows account does not have any access to the SQL Server.  I read about the undocumented proc to reset the owner of the package, but why does it not do it right the first time?

    Thanks1

  • January 15, 2004 at 7:54 am

    #490201

    To completely lock down your DTS sub-system, go through the system stored procedures in msdb and revoke execute permission to public on any procedure that has dts in its name.

    Then create a DTSUser in msdb and grant execute permission to all the stored procedures revoked above.  This way only users who login into SQL Server as DTSUser will be able to create, view, modify or execute DTS packages.

    If you want to allow all users to be able to view only then you need to grant execute permission to the public role in msdb on all the sp_enum... and sp_get.... system stored procedures.

    I hope this helps.

     


    Joseph

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

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