July 11, 2016 at 12:20 pm
Hello all,
I have a scheduled job that occasionally (once in a blue moon) needs to be run on demand. This request usually comes from a group of business users who do not have permissions to run jobs, nor should they.
Rather than having a Developer run the job, it would be 'nice' if we could have the business user run it. My idea was to embed the job within a stored procedure, but there is not an option to assign Windows Authentication credentials with sp_start_job.
Ideally, there could be a hack to do this, so that there is not a need to set up SQL Server Agent Roles. Again, this is an outlier issue, so building infrastructure to support is seems too intensive.
Any clever ideas?
Andrew Harkins
July 11, 2016 at 1:37 pm
aharkins (7/11/2016)
Hello all,I have a scheduled job that occasionally (once in a blue moon) needs to be run on demand. This request usually comes from a group of business users who do not have permissions to run jobs, nor should they.
Rather than having a Developer run the job, it would be 'nice' if we could have the business user run it. My idea was to embed the job within a stored procedure, but there is not an option to assign Windows Authentication credentials with sp_start_job.
Ideally, there could be a hack to do this, so that there is not a need to set up SQL Server Agent Roles. Again, this is an outlier issue, so building infrastructure to support is seems too intensive.
Any clever ideas?
Andrew Harkins
No idea. However I would try EXECUTE AS in the stored procedure
July 11, 2016 at 2:01 pm
While logged in as sysadmin, create a stored proc that includes "WITH EXECUTE AS OWNER". In that proc, have the command:
EXEC msdb..sp_start_job @job_name = @job_name, ...
Then GRANT the role(s)/user(s) execute authority on that stored proc.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply