DTS execution by user from an app. or SP

Question

DTS execution by user from an app. or SP

eturner

SSC Journeyman

Points: 82
More actions
August 10, 2001 at 1:21 pm

#104501

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?

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

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

Sean Burke SSCommitted Points: 1504 More actions · Answer 1

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

eturner SSC Journeyman Points: 82 More actions · Answer 2

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.

eturner SSC Journeyman Points: 82 More actions · Answer 3

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.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

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

SQLZ SSChampion Points: 12872 More actions · Answer 5

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

Karl
source control for SQL Server

eturner SSC Journeyman Points: 82 More actions · Answer 6

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.

satrigney SSC Eights! Points: 926 More actions · Answer 7

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