August 10, 2001 at 1:21 pm
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?
August 10, 2001 at 2:58 pm
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
September 4, 2001 at 9:25 am
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.
September 4, 2001 at 9:25 am
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.
September 4, 2001 at 11:44 am
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
September 5, 2001 at 7:38 am
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
September 6, 2001 at 9:37 am
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.
September 17, 2001 at 1:10 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply