How to enable a non-SysAdmin user to start a job

I thought I could do this by granting permission to a few MSDB
procedures:
grant execute on sp_help_jobhistory to UserRole -- view job history
grant execute on sp_help_job to UserRole -- view job
grant execute on sp_start_job to UserRole -- start job
but I get an error:
Server: Msg 14262, Level 16, State 1, Line 1
The specified @.job_name ('Name Of Job') does not exist.
I looked at the sysjobs_view in MSDB (below) it looks like only the
owner, SysAdmins and TargetServersRole (what is this?) can view the
jobs. If I alter this view to include my UserRole this allows them to
start the job, but I don't want to do this because it is a hack.
SELECT *
FROM msdb.dbo.sysjobs
WHERE (owner_sid = SUSER_SID())
OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
What is a better solution?We got around this by using a queue to disconnect the security.
1) create a queue table that takes the name of a job and a status value.
2) create a stored procedure that can read the queue, find any pending jobs,
run sp_startjob for any pending jobs, mark started jobs as complete.
3) create SQL Agent job owned by SA that runs once every minute and runs
that stored procedure.
4) user inserts job name in queue and waits ~1 min.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Laurence" <laurencen@.eurostop.co.uk> wrote in message
news:1161622953.615964.21000@.k70g2000cwa.googlegroups.com...
>I thought I could do this by granting permission to a few MSDB
> procedures:
> grant execute on sp_help_jobhistory to UserRole -- view job history
> grant execute on sp_help_job to UserRole -- view job
> grant execute on sp_start_job to UserRole -- start job
> but I get an error:
> Server: Msg 14262, Level 16, State 1, Line 1
> The specified @.job_name ('Name Of Job') does not exist.
> I looked at the sysjobs_view in MSDB (below) it looks like only the
> owner, SysAdmins and TargetServersRole (what is this?) can view the
> jobs. If I alter this view to include my UserRole this allows them to
> start the job, but I don't want to do this because it is a hack.
> SELECT *
> FROM msdb.dbo.sysjobs
> WHERE (owner_sid = SUSER_SID())
> OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
> OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
>
> What is a better solution?
>|||I see there is a way to do this by adding the user to the
TargetServersRole in MSDB, although it has a downside. See here:
http://www.mcse.ms/message638764.html
Daniel Jameson wrote:
> We got around this by using a queue to disconnect the security.
> 1) create a queue table that takes the name of a job and a status value.
> 2) create a stored procedure that can read the queue, find any pending jobs,
> run sp_startjob for any pending jobs, mark started jobs as complete.
> 3) create SQL Agent job owned by SA that runs once every minute and runs
> that stored procedure.
> 4) user inserts job name in queue and waits ~1 min.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
> "Laurence" <laurencen@.eurostop.co.uk> wrote in message
> news:1161622953.615964.21000@.k70g2000cwa.googlegroups.com...
> >I thought I could do this by granting permission to a few MSDB
> > procedures:
> >
> > grant execute on sp_help_jobhistory to UserRole -- view job history
> > grant execute on sp_help_job to UserRole -- view job
> > grant execute on sp_start_job to UserRole -- start job
> >
> > but I get an error:
> >
> > Server: Msg 14262, Level 16, State 1, Line 1
> > The specified @.job_name ('Name Of Job') does not exist.
> >
> > I looked at the sysjobs_view in MSDB (below) it looks like only the
> > owner, SysAdmins and TargetServersRole (what is this?) can view the
> > jobs. If I alter this view to include my UserRole this allows them to
> > start the job, but I don't want to do this because it is a hack.
> >
> > SELECT *
> > FROM msdb.dbo.sysjobs
> > WHERE (owner_sid = SUSER_SID())
> > OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
> > OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
> >
> >
> > What is a better solution?
> >