Exam DP-300 topic 2 question 31 discussion

As Gocsan said, I tested by using his settings. Yes, SQLAgentUserRole can successfully backup the database from a scheduled job. The answer is C, SQLAgentUserRole.

The SQLAgentUserRole allows users to create and manage their own jobs but does not grant them access to jobs created by others. This role provides the necessary permissions without giving excessive privileges.

Unfortunately user need "to perform a full backup of DB1" and this is only possible with "db_owner" role. If the goal is to strictly adhere to the principle of least privilege, you would ideally create a custom role or grant specific permissions tailored to the exact needs of User1. User need write permision for backup. So, yes, question is successfully made confusing!

SQLAgentUserRole Control permission for job and schedules that they own. SQLAgentReaderRole All permission of SQLAgentUserRole plus permission to view the list of all available jobs and schedules. SQLAgentOperatorRole Permission to manage local jobs and schedules. View properties for operators and proxies and enumerate all available proxies and alerts

Question is about least permissions. So Answer is C SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles.

Operator for receiving the notifications, the answer is C

C, SQLAgentUserRole (least privilege)

Operator for receiving the notifications, the answer is C

C, SQLAgentUserRole I did test this in the SQL server on Azure VM, You have to assign the user the db-Backupoperate role on the target database. SQLAgentUserRole is sufficient to create/schedule a sql agent job

C, SQLAgentUserRole (least privilege)

I just did real tests. All of these 3 roles, SQLAgentReaderRole, SQLAgentUserRole, and SQLAgentOperatorRole can NOT create a database backup if the user is NOT in the "db_backupoperator" role on the target DB. Those 3 roles only give the user to create a scheduled job but the user doesn't have the permission to create a DB backup. I got the following error: Executed as user: TESTER. The server principal "TESTER" is not able to access the database "sysdb" under the current security context. [SQLSTATE 08004] (Error 916) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. You have to assign the user the db-Backupoperate role on the target database. If the user is in the db_owner role, the user can do the database backup but the user can not set up a scheduled job if it is not in one of those 3 SQLAgent Roles. It is supprise to me. If I am wrong, please let me know. Thanks

I've tested this out by actually setting up a test user that has db_backupoperator role on a test DB and has the SQLAgentUserRole role on the msdb DB. I then logged in as the test user to set up a scheduled job that performs a backup of the test DB. It succeeds, so the SQLAgentUserRole is all that is needed, which is the least privilege role.

D. SQLAgentOperatorRole

D. SQLAgentOperatorRole The "SQLAgentOperatorRole" role allows the user to perform various SQL Server Agent operations, including creating and modifying jobs and schedules. This role provides the necessary permissions for User1 to create a scheduled task to perform a full backup of DB1, but no more than what is needed.

If you use windows scheduled task for the backup effort, then the provided answer will make sense. But I am not sure if this is where this question is intended to go.

Correction: Ans: C

Ans B. SQLAgentUserRole allows users to create jobs and to manage jobs that they create. It didn't say that you need to run other jobs that you don't own.