SQL Server Performance Forum – Threads Archive

DTS package permissions

I had an argument with my coworker on granting privileges to a developer on a production box, to build a DTS package. Well, my question was that can a developer having a NT login authentication, with select permissions on all the tables in the database, can he build a dts package successfully with no permission problems ? My answer is â€˜yes#%92, it is possible. Any thoughts on this, from anybody in the forum? Thanks in advance!
RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

BOL refers
Package Scheduling and Security Issues
Usually, a package run from DTS Designer, the DTS Import/Export Wizard, the DTS Run utility, or from the command prompt executes under the security context of the user who is currently logged in. However, a package scheduled for execution runs under the security context of the SQL Server Agent job that runs the package. The owner of that job may or may not be the same as the user currently logged in. Consider the following types of ownership: For packages created under a Microsoft Windows NTÂ® 4.0 or Microsoft WindowsÂ® 2000 account, the job runs under the security context of the account that started SQL Server Agent.
If the job is owned by a login belonging to the sysadmin fixed server role, the security context of the package defaults to the account used to start the local SQL Server Agent. If the server is registered using Windows Authentication, the owner of the job is the account of the SQL Server Agent. If the server is registered using SQL Server Authentication, the owner of the job is that SQL Server login.
If the job is owned by a login that is not a member of the sysadmin fixed server role, the package runs under the context of the job step proxy account, with the rights and permissions of that account.
Ownership conflicts can generate the following types of problems: File paths specified in the package may not be visible in a different security context. That is, a different user executing the package may not have access to the same share points as the package creator (for example, the user may not have the drive letters of the package creator mapped). To guard against this problem, use Universal Naming Convention (UNC) names rather than file paths when specifying external files.
The owner of the SQL Server Agent job that runs the package does not have permission to access the paths pointed to or connections made in the package. For example, the owner of the job may only have local server access. If this problem arises, view the security context of the job in SQL Server Enterprise Manager and log out of that instance of SQL Server. Then log back in to that same instance of SQL Server using the security context of the job and attempt to run the package.
For packages that call COM components in Microsoft ActiveXÂ® scripts, the called components must exist on the same workstation on which the package is running. Also, the SQL Server Agent job account must have permission to run the job.
For all of the above situations, copying external files used by the package onto the same server as the executing package may preempt package failures caused by ownership problems. In cases where COM components are used by a scheduled package, the called components must be loaded onto the same computer on which the instance of SQL Server is installed, and SQL Server Agent must have permission to use the objects. Otherwise, the package will not execute successfully. Important If you schedule a DTS package with a user password instead of an owner password, the scheduled job will not report a failure unless the package is set to fail on the first failed step. This is because the user does not have permission to read the package status after the package is run. This behavior will not occur if the package is scheduled using the owner password.

I’ve replied to similar question in last few days, refer for more information. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>