April 10, 2006 at 9:39 am
I am currently trying to initiate a cube build from a DTS. I had to give the log in Sysadmin privilages to perform the exec from a stored procedure. There has got to be a better way.
What I would like to do is just give them permissions to run that one DTS from that log in. kind of like Proc execution privilages. I am getting more and more requests so there has got to be a better way.
Any help is appreciated
April 10, 2006 at 9:49 am
The problem is DTS executes through the use of an executible, dtsrun.exe. Therefore, from within a stored procedure the only way to do this is to you xp_cmdshell... not exactly a solution most folks like.
The usual workaround is to have a poller job running SQL Server Agent. The login has the ability to execute a stored procedure which places something in a given table. The poller job looks in the table and if it sees the right entry, it fires off the package. There is, of course, a delay as the poller job only runs every so often, but it keeps you from having to give permissions to xp_cmdshell out.
K. Brian Kelley
@kbriankelley
April 10, 2006 at 12:16 pm
I am confused then. why am I passing the admin password in the script
exec
master.dbo.xp_cmdshell 'DTSRUN /S SQLPDC /U DW_MASTER /P Password /N PPC ', NO_OUTPUT
If that log in has sysadmin rights I would think that is what is executing the DTS
April 10, 2006 at 12:24 pm
The DTSRun utility is making a connection back to the SQL Server you have specified with the /S parameter. The DTS package is stored in the msdb database and as a result DTSRun needs to know how to connect back to SQL Server. If you want to execute under the context of the SQL Server service account, drop the /U and /P parameters and replace them with a /E, which specifies integrated security.
K. Brian Kelley
@kbriankelley
April 10, 2006 at 12:58 pm
I would create a job you run your DTS package without (or with if you want to happen periodically anyway) and set the job as owned by the account you are using to fire the SP. Now you just use sp_start_job to run the job and thus the DTS package. This way you can tighten security as much as possible and have a better mechanism to track errors.
See BOL about sp_start_job (in msdb) for details on permissions and the reason I say set the job as owned by that specific account.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply