April 17, 2012 at 2:28 am
Hi all,
I'm trying to create a procedure that will allow a user to execute a specific pair of SQL Agent jobs, without me having to grant rights on sp_start_job to that user. My first stab was to use the with execute as owner option:
use DBA
go
create procedure dbo.RunAgentJobs (
@jobtype varchar(20)
)
with execute as owner
as
begin
-- check that the input value is in a range of values that we expect
if @jobtype not in ('JobA','JobB')
begin
raiserror ('The input parameter should be either ''JobA'' or ''JobB''', 18, 1)
return
end
-- must be good if we get to here
declare @jobname sysname
set @jobname = cast(replace('RunJob<placeholder>Foo','<placeholder>',@jobtype) as sysname)
exec msdb..sp_start_job @job_name = @jobname
end
This failed with the error "The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'." Note that this fails if I run it as well and I'm admin on the server. It also fails if I use with execute as 'username', where username has rights on sp_start_job.
So, I tried another route, using impersonation:
---------------------------------------------------------------------
use master
go
create login [sp_start_job_user] with password ='password'
go
grant impersonate on login ::[sp_start_job_user] to [someuser]
go
---------------------------------------------------------------------
use msdb
go
create user [sp_start_job_user] for login [sp_start_job_user]
go
grant execute on dbo.sp_start_job to [sp_start_job_user]
---------------------------------------------------------------------
use DBA
create user [sp_start_job_user] for login [sp_start_job_user]
go
---------------------------------------------------------------------
use DBA
go
alter procedure dbo.RunAgentJobs (
@jobtype varchar(20)
)
as
begin
-- check that the input value is in a range of values that we expect
if @jobtype not in ('JobA','JobB')
begin
raiserror ('The input parameter should be either ''JobA'' or ''JobB''', 18, 1)
return
end
-- must be good if we get to here
declare @jobname sysname
set @jobname = cast(replace('RunJob<placeholder>Foo','<placeholder>',@jobtype) as sysname)
execute as login = 'sp_start_job_user'
exec msdb..sp_start_job @job_name = @jobname
end
This fails with the error "the specified @job_name('jobname') does not exist." The @job_name parameter passed into sp_start_job is a valid name of a job that does exist on the server. The error is also thrown if I pass the parameter as plain varchar.
Any suggestions as to what my problem might be (or alternative approaches) are welcomed.
Thanks, Iain
April 17, 2012 at 11:20 am
I've done this by creating a SQL Server login, adding it as a user in msdb,and making it a member of SQLAgentUserRole. This gave it permission to execute sp_start_job.
I added the user to the database with the job run stored procedure and used
"execute as .
Regarding the "job does not exist" error, make sure you're setting @jobname to the correct value. I ran the "set @jobname" bit with @jobtype set to "JobA" and the job name returned was "RunJobJobAFoo".
Greg
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply