DP-300 Question #272: Real Exam Question with Answer & Explanation

Question

You have an Azure subscription that contains an instance of SQL Server on an Azure virtual machine named SQLVM1 and a user named User1. SQLVM1 hosts a database named DB1. You need to ensure that User1 can create a scheduled task to perform a full backup of DB1. The solution must use the principle of least privilege. Which built-in database role should you assign to User1?

Options

• Adb_owner
• BSQLAgentReaderRole
• CSQLAgentUserRole
• DSQLAgentOperatorRole

Explanation

Explanation

SQLAgentOperatorRole is correct because it grants users the ability to create, modify, and execute SQL Agent jobs (including scheduled backup tasks) while adhering to the principle of least privilege - it provides just enough permission to manage jobs without granting excessive administrative rights.

Why the others are wrong:

• db_owner (A) gives full control over the database, which is far more than needed for scheduling a backup job - this violates least privilege.
• SQLAgentUserRole (C) is too restrictive; it only allows users to manage their own jobs and cannot execute jobs owned by others or create jobs that run under proxy accounts needed for backups.
• SQLAgentReaderRole (B) only provides read access to view job information - users cannot create or schedule any jobs with this role.

💡 Memory Tip: Think of the SQL Agent roles as a hierarchy - User < Reader < Operator < Owner. For creating and scheduling tasks, you need Operator-level access, which sits in the "sweet spot" between read-only Reader and the overpowered db_owner. If you need to do, think Operator!

No community discussion yet for this question.