November 18, 2011 at 5:30 am
Hi everyone
I have a web application that calls an unscheduled job that in turns runs a SSIS package.
The web application uses a limited user account, but to be able to call the start_job sp I had to make it part of the SQLAgentOperatorRole
Is this a security threat, or an acceptable practice?
Thank you.
November 18, 2011 at 6:12 am
Jako de Wet (11/18/2011)
I have a web application that calls an unscheduled job that in turns runs a SSIS package.The web application uses a limited user account, but to be able to call the start_job sp I had to make it part of the SQLAgentOperatorRole
Can you assign ownership of that particular job to the user account the application uses to connect? That way the application account will be able to start the job without granting further privileges to it.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 18, 2011 at 6:16 am
Hi
Yes I can do that, but then I just have to make sure that account has enough privileges to run the job?
Thank you.
November 18, 2011 at 6:20 am
could grant execute on sp_start_job only to the account in question, then pass it the job_id
November 18, 2011 at 6:22 am
Jako de Wet (11/18/2011)
Yes I can do that, but then I just have to make sure that account has enough privileges to run the job?
Try it 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 18, 2011 at 6:23 am
anthony.green (11/18/2011)
could grant execute on sp_start_job only to the account in question, then pass it the job_id
Wouldn't work. Does some checks internally to make sure the user is a member of one of the built-in SQLAgent roles. You could DENY execute permissions on everything except start job.
-- Gianluca Sartori
November 18, 2011 at 6:25 am
I tried that, reset the privelegs on the user account and made it it the owner, but now it doesn't want to execute the dbo.sp_start_job procedure
will grant it execute access to the sp and see what happens:)
November 18, 2011 at 6:30 am
ok, I set the ownder of the job to the user account running it and granted execute access to the sp_start_job procedure, but the job fails with the following:
Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.
November 18, 2011 at 6:36 am
Here's an alternative using alerts ...
help to tighten use of cmdshell or sp_start_job[/url]
You could also set up a SSB solution for it.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply