Interview Questions

1. How can we grant permissions on the SQL Server agent jobs and which roles are available?

SQL Server contains 3 fixed database roles on the MSDB database, which gives administrators fine control over access to SQL Server Agent. The SQL Server Agent node in SSMS is visible only to users in one of these 3 roles (except sysadmins, who can see everything irrespective of role membership). Here is an explanation of the roles, in order from the most restrictive to least restrictive:

SQLAgentUserRole – Users in this role are granted view/edit/delete/execute access to only jobs owned by them. Users in this role cannot view any jobs owned by system administrators, or by users in the other two roles. Grant this role when you want users to only see jobs owned by them.

SQLAgentReaderRole – Users in this role get all the privileges of theSQLAgentUserRole, i.e. they get access to owned jobs. In addition to that, they can also view (but not modify or execute) all jobs on SQL Server Agent, irrespective of ownership. Grant this role when you want users to be able to view, but not execute, all jobs in the system, but modify/execute only jobs owned by them.

SQLAgentOperatorRole – Users in this role get all the privileges of the SQLAgentReaderRole. In addition to that, they can also execute, or enable/disable any job in the system. However, users in this role can modify only owned jobs. Grant this role for super users who can view/execute all jobs on the system. 

By Hena Parveen 2 0
Is this helpful? Yes No

Submit an interview question

Submitted questions and answers are subject to review and editing, and may or may not be selected for posting, at the sole discretion of w3Sniff.

Get Started

Comments

Leave a Comment


Check out more interview questions

Based on your skills

MS SQL Server

1663027 1720 23 859

Angular

42508 92 0 46

Wordpress

34330 72 1 36