February 21, 2003 at 8:11 am
In order to allow developer to use only DTS designer to design DTS packages, what security setting in SQL Server have to be configured properly? Thanks.
February 21, 2003 at 10:34 am
There is no security on DTS Designer. To open a package from SQL Server, they need access to msdb and the package password.
Steve Jones
March 11, 2003 at 12:26 pm
I found this on this same forum some time ago and has proved to be very useful to me. These are not my words and would like to give the merit to the owner, but I don't remember who was. Anyway, here it is:
"Did you know that anyone with a valid login to your SQL Server can see and execute all DTS packages on that server via Enterprise Manager? They can also add as many new DTS packages as they want. This is because access to the stored procedures that perform these activities defaults to the public role.
To block this kind of activity, remove the execute permissions from the following stored procedures found in the msdb database:
sp_add_dtspackage
When permissions are removed from this stored procedure standard users can no longer add DTS packages.
sp_enum_dtspackages
When permissions are removed from this stored procedure standard users can no longer see existing DTS packages.
sp_get_dtspackage
When permissions are removed from this stored procedure standard users can no longer open nor execute existing DTS packages.
After doing this you can add more security by resaving each DTS Package and assigning a owner password and/or a user password.
Let me give credit to the authors of the book SQL Server 2000 DTS (Brian Knight is one of them) for explaining this to me and pointing me to these three stored procedures. "
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply