November 18, 2009 at 5:01 am
I wish to give user permissions to administer, run and edit jobs but nothing else on the server. Is there an easy way to do this?
November 18, 2009 at 7:55 am
Assign these roles to the user/login..
SQLAgentOperator, SQLAgentReader, and SQLAgentUser
SQLAgentOperatorRole Permissions
SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server.
SQLAgentOperatorRole members have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server. They can also enable or disable all local jobs and schedules on the server. To enable or disable local jobs or schedules, members of this role must use the stored procedures sp_update_job and sp_update_schedule. Only the parameters that specify the job or schedule name or identifier and the @enabled parameter can be specified by members of SQLAgentOperatorRole. If they specify any other parameters, execution of these stored procedures fails. SQLAgentOperatorRole members cannot change job ownership to gain access to jobs that they do not already own.
IF NT Account be careful with case sensitive DOMAIN\ACCOUNT and domain\account as we recently hit an issue where the user could not edit their own jobs and it took me while to notice the case difference.. see article here...
Oraculum
November 18, 2009 at 9:22 am
oraculum (11/18/2009)
Assign these roles to the user/login..SQLAgentOperator, SQLAgentReader, and SQLAgentUser
I think oraculum meant to say you should assign one of the roles to the user, since, as he says, they are progressively more privileged and include the permissions of the previous one.
From least privileged to most privileged:
SQLAgentUserRole,
SQLAgentReaderRole,
SQLAgentOperatorRole
You have to add the user to msdb before you can make them a member of one of the roles.
Greg
November 18, 2009 at 9:29 am
Yes thanks Greg, was rushing a bit with that one!
One thing also to note... you say you only want them to have permissions to run and edit the jobs.. and nothing else on the server??
.. if they are to manually start a job, the jobs executes as the user who is logged in and started it and not the SQL Agent Service account. This can cause problems if the steps within the job require access to other objects and data within a database.
If the job is scheduled it will by default be run by the service account, unless the Step has a 'Run As' defined for a proxy account.
Oraculum
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply