January 17, 2010 at 5:01 am
hi,
how to give permission to user to excute dts packages in sql2000.
thanks,
kiran
February 4, 2010 at 6:20 am
Securing SQL Server 2000 Data Transformation Services (DTS) packages is important, especially if you work with DTS a lot. By default, anyone with a login to SQL Server can create a DTS package and save it to SQL Server. But you don't want everybody who has a login to create new DTS packages or execute existing packages, particularly on a production system. In addition, if you don't control the creation and execution of DTS packages, the msdb database can grow too large for you to manage.
You can restrict access to DTS packages by assigning an owner password and user password. An owner password prevents users from opening (and thus from editing or changing) a package. A user password prevents users from executing a package. If you set a user password, you must also set an owner password. But you can set an owner password without a user password. You can specify that users supply these passwords when they save a DTS package in the DTS Designer or when they create a DTS package by using the DTS Import or Export wizards.
But you can require owner and user passwords only when users save DTS packages to SQL Server or as a structured storage file. Passwords aren't an option when users store DTS packages to Meta Data Services or Visual Basic File storage types. But other methods exist for securing your DTS packages.
If any users have access to the sysdtspackages table in the msdb database, you can prevent them from viewing DTS packages on the server by denying SELECT permissions on sysdtspackages. (By default, the public role doesn't have SELECT permissions on this table.) You can also prevent users from viewing DTS packages by denying EXEC permissions on the undocumented sp_enum_dtspackages stored procedure in the msdb database. This stored procedure lists the DTS packages that are available on the server. By default, the public role has EXEC permissions for this stored procedure.
By default, the public role also has EXEC permissions on the undocumented sp_add_dtspackage stored procedure, which resides in the msdb database. If users have permissions for this stored procedure, they can use it to add or create DTS packages. To prevent users from adding DTS packages, you can deny EXEC permissions on this stored procedure.
We started using these techniques when we observed a lot of DTS packages on our server that had names such as TestPackage_V1, TestPackage_V2, and TestPackage_V3. We realized that users were testing their DTS skills by creating these packages. At that point, we decided we needed security for our DTS packages. By keeping owner passwords and using the other techniques described above, we could prevent users from opening or creating DTS packages.
We suggest that you assign a couple of DBAs to create DTS packages and keep one owner password for all the packages (remembering separate passwords for all the packages would be too cumbersome). Memorize your password as you do the sa password. These techniques have effectively helped us secure our DTS packages.
—Krishna Sonti
End of Article
DTS Packages and Security:
It is important to secure your DTS packages, so that no unauthorized users can run or modify those packages. When saving DTS packages to SQL Server, you will get an option to specify a user password and an owner password. Setting a user password ensures that only those people who know the password can execute the package. Once the owner password is set, one cannot modify the package without knowing this password
It is very likely that some of the files you generate using DTS contain confidential information, in which case pay special attention to where these files are getting written to. Output these files to a secure location and make sure only authorized users have access to those files and folders. This can be controlled by setting NTFS permissions at the Operating System (OS)/Windows level.
vyaskn@hotmail
I hope this helps
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply