Re: Is it possible to grant a login the permission to create and schedule jobs?

Michael,

Yes it is possible, as long as the user is the job owner. The BOL says that
sp_add_job is public, which means that anyone who is a user of msdb (which
you may not have granted to everyone) should be able to create a job. So
first, the user needs rights to msdb.

*** SQL 2000 - I remember that we needed to do more, as show below, but no
longer remember all the reasons. :)

You can create a role, such as AgentJobManager and grant specific rights.
Users will (as non-sysadmins) be limited to managing the jobs that they
personally create.

GRANT EXECUTE ON sp_add_category TO AgentJobManager
GRANT EXECUTE ON sp_add_job TO AgentJobManager
GRANT EXECUTE ON sp_add_jobschedule TO AgentJobManager
GRANT EXECUTE ON sp_add_jobstep TO AgentJobManager
GRANT EXECUTE ON sp_delete_category TO AgentJobManager
GRANT EXECUTE ON sp_delete_job TO AgentJobManager
GRANT EXECUTE ON sp_delete_jobschedule TO AgentJobManager
GRANT EXECUTE ON sp_delete_jobstep TO AgentJobManager
GRANT EXECUTE ON sp_post_msx_operation TO AgentJobManager
GRANT EXECUTE ON sp_update_category TO AgentJobManager
GRANT EXECUTE ON sp_update_job TO AgentJobManager
GRANT EXECUTE ON sp_update_jobschedule TO AgentJobManager
GRANT EXECUTE ON sp_update_jobstep TO AgentJobManager
GRANT SELECT ON syscategories TO AgentJobManager
GRANT SELECT ON sysjobsTO AgentJobManager
GRANT SELECT ON sysjobserversTO AgentJobManager

Then make the necessary users member of the AgentJobManager role.

*** SQL 2005

Make the users members of one of the following roles, according to what
rights you want them to have:
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole

RLF
"Michael MacGregor" <nospam@xxxxxxxxxx> wrote in message
news:uxaKhSVyHHA.4004@xxxxxxxxxxxxxxxxxxxxxxx

I have a user who does not need to have any other server permissions other
than to create and schedule jobs, but I'm going round in circles trying to
figure out if I can actually do this using BOL as a resource. Is it
possible and if so, how?

TIA

Michael MacGregor
Database Architect



.