I was asked to document the permissions to enumarate, modify and execute DTS packages which we have on our SQLServer 2005 production server(s).
I know that the user needs read-access to sysdtspackage to list the DTS package(s), the user also needs to be the Owner of the package or sysadmin inorder to modify the package.
Now, there are three ways of running a DTS package stored in the msdb
 Run the package from the DTS designer
 Run from the command line via DTSRun
 As a DTS Task in SSIS
I have a DTS package (ExtractWestCoast.dts) which I wanted to test for permissions. So, I created a login named DTSTest and gave it db_datareader role to the msdb database (Note that I also have a Windows Authentication based login - CorpDomain\grajee -
into the sqlserver with sysadmin role). With this role given, I logged into SSMS using DTSTest and made sure I was able to see the list of DTSPackages. I did a sample run of ExtractWestCoast DTSpackage expecting it to fail. But to my surprise the DTS package
executed successfully. Using my regular id (CorpDomain\grajee), I enabled the logging feature on the DTS package so that it creates entries in the sysdtslog msdb system table for every run to see under whom the package runs. Using the DTSTest login, I
executed the DTS package and it executed successfully. I checked the
View Complete Post