1.

What are SQL Server Agent Fixed Database Roles with the definition?

Answer»

SQL Server agent has lots of features and sometimes you need to give rights on an agent to manage and view jobs. Allowing every one with sysadmin permission is not appropriate.

SQL Server has 3 fixed DB roles for permission on SQL Agent & Jobs.

  • SQLAgentUserRole – Users with SQLAgentUserRole rights can manage Agent jobs CREATED by SELF. Self-owned jobs can be managed by this rights, he can not view other jobs AVAILABLE on the server.
  • SQLAgentReaderRole – This is the 2nd level of permission on the agent. USER with SQLAgentReaderRole rights can only view job available on server but cannot change or modify any jobs. This roll gives the ability to review multi-server jobs, their configurations, and history with SQLAgentUserRole rights.
  • SQLAgentOperatorRole - SQLAgentOperatorRole  roles gives you highest permission on agent. User with this role has the ability to review operators, proxies and alerts, execute, stop or start all local jobs, delete the job history for any local job as well as enable or disable all local jobs and schedules with AQLAgentReaderRole rights.


Discussion

No Comment Found