DTS execution by user from an app. or SP

  • Hi All:

    Has anyone successfully allowed a user to execute a DTS package from a stored procedure

    or application without the user being a member of the sysadmin role? or without opening up xp_cmdshell to non-sa's?

  • We were just talking about this subject yesterday, but haven't yet put it to the test. The easiest way we thought of to do this would be to create a job to run the DTS Package, then have your SP start the job.

    It may get a bit more complcated if you have to pass parameters and such.

    Hope this helps,

    Sean

  • I tried your suggestion and it does work, provided that the user has 'sysadmin' or the user who executes the SP is sa. I also changed the ownership of the scheduled task to a user. The SP runs, but doesn't have permissions to execute sp_start_job, because the user is not sa, and it attempts to use the proxy account instead(SQLAgentCmdExec) for security, which I could open up, but this would have the same effect as opening up xp_cmdshell to non-sa's, which I don't want to do. So I'm still looking for a solution, if anyone out there has one.

  • I tried your suggestion and it does work, provided that the user has 'sysadmin' or the user who executes the SP is sa. I also changed the ownership of the scheduled task to a user. The SP runs, but doesn't have permissions to execute sp_start_job, because the user is not sa, and it attempts to use the proxy account instead(SQLAgentCmdExec) for security, which I could open up, but this would have the same effect as opening up xp_cmdshell to non-sa's, which I don't want to do. So I'm still looking for a solution, if anyone out there has one.

  • Instead of letting a user run sp_start_job, let another job do it. Let the user insert a row in a table and then have a job run every minute or 5 minutes or whatever. This job will check the table and run the process.

    I built this in v6.5 and it worked well to let users run processes without sa rights. We have multi step jobs, results, etc. It can be a big project if you want it.

    Steve Jones

    steve@dkranch.net

  • The users do not have to be sysadmins to run the job. You can add them to the TargetServersRole role in msdb and this will allow users to run sp_start_job.

    Having said that, that also means that the users will have permissions to run any job, which you might not want.

    So it looks like Steve's suggestion is a good alternative.

    Still, it's another option you have to consider.

    Regards,

    Karl Grambow

  • Thanks for all the input. I already knew I could do this Steve's way. But I was looking

    for a way to allow users to do this from an application, via a stored procedure, and on-demand.

    Karl's method also works well but it does create somewhat of a security hole. I suspect an application role for this with permissions to msdb may eliminate this hole.

    Thanks for your inputs.

  • Darren Green has an article on swynk that shows you how to use the OLE Stored Procedures to call a DTS Package from a stored procedure. I have tested this and have gotten it to successfully run.

    http://www.swynk.com/friends/green/dtsole.asp

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply