Exam DP-300 All QuestionsBrowse all questions from this exam
Question 157

HOTSPOT

-

You have an Azure subscription that contains an instance of SQL Server on Azure Virtual Machines named SQLVM1 and a user named User1. SQLVM1 hosts a database named DB1.

You need to ensure that User1 can perform the following tasks on DB1:

• Create jobs.

• View all jobs.

• Modify, delete, and disable the jobs the user created.

The solution must use the principle of least privilege.

Which built-in database role should you assign to User1, and where is the role defined? To answer, select the appropriate options in the answer area.

    Correct Answer:

Discussion
Daemon69

Location: msdb Role: SQLAgentUesrRole

Sr18

Role: SQLAgentReaderRole this is least privilege role to see all jobs SQLAgentUserRole can only view jobs they own.

Daemon69

Location: msdb Role: SQLAgentOperatorRole • Create jobs. • View all jobs. • Modify, delete, and disable the jobs the user created.

U_C

The answer is correct. SQLAgentUesrRole can not view all jobs. Gosan is right!

Gocsan

Based on the table here https://www.youtube.com/watch?v=rodn5MSeiuY at 0m:40s and using the principal of least privilege, the answer should be SQLAgentReaderRole. SQLAgentUserRole cannot view all jobs, it can only view own jobs. Although both SQLAgentReaderRole and SQLAgentOperatorRole can create/modify/delete own jobs, the next thing to consider here is that the user must be able to disable its own job. SQLAgentReaderRole can disable own job, where SQLAgentOperatorRole can disable all jobs. Using the principal of least privilege, the answer is SQLAgentReaderRole.

bsk1983

Tested it and answer is correct, msdb, SQLAgentReaderRole, because 2nd option view all jobs is only available if account has SQLagentReaderole and higher (operator) so, with minimum privilege as requirement, Answer is msdb, SQLagentReaderRole

KingChuang

Localtion:msdb Role:SQLAgentReaderRole (Because question request "view all jobs". SQLAgentUserRole only view owned jobs)

KingChuang

Ref: https://learn.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server-ver16#sqlagentreaderrole-permissions

CloudTech@2023

owned jobs only is for ReaderRole & nor for User Role - is it right?

CloudTech@2023

owned jobs only >> for ReaderRole & not for UserRole - is it right? so i consider that SQLAgentUserRole is the right answer