September 26, 2005 at 9:37 pm
I created a stored procedure that starts a SQL job owned by SA. I granted execute permission on the stored procedure to a SQL Server id with no special permissions. When I try to execute the stored procedure I receive error 14262 - The specified @Job_Name ('TestJob') does not exist.
I thought the stored procedure would start the job since the stored procedure is owned by SA, but apparently ownership chaining does not affect scheduled jobs.
I then added the SQL Server login id to TargetServersRole in msdb and granted Execute permission against sp_start_job to TargetServersRole. This appears to have addressed the issue, however I'm not sure if I opened any security holes by doing this. Any thoughts?
Thanks, Dave
September 27, 2005 at 9:28 am
This is very strange... Why would you want to give permission to a login who has no permission to start a job that was owned by sa?
If it is own by sa, why not set it to run from sql agent as sa to start anyjob you want according to the time you want it to start?
mom
September 27, 2005 at 9:43 am
The job is to be invoked by a nightly batch process. The time it will be invoked each night depends upon how quickly the nightly batch process runs. I was trying to treat the job in a manner similar to how stored procedure security works with ownership chaining. A user does not have access to tables in a database, but the user does have the ability to run a stored procedure, which was created by dbo and therefore can access the tables. Instead of having the stored procedure access tables I wanted it to start a SQL job, owned by SA. Unfortunately the ownership chain does not come into play with scheduled jobs.
Dave
September 27, 2005 at 12:12 pm
One thing I've read about TargetServersRole is that it's not guaranteed to be supported by MS in the future. It's security was changed in SP3a and I'm sure that threw some people who used it.
Greg
Greg
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply