DP-300 · Question #285
Hotspot Question 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…
The correct answer is Location:: msdb; Built-in role:: SQLAgentUserRole. This question tests knowledge of SQL Server Agent fixed database roles and where they are defined within the msdb system database, applying the principle of least privilege for job management.
Question
Exhibit
Answer Area
- Location:msdbDB1mastermsdb
- Built-in role:SQLAgentUserRoleSQLAgentOperatorRoleSQLAgentReaderRoleSQLAgentUserRole
Explanation
This question tests knowledge of SQL Server Agent fixed database roles and where they are defined within the msdb system database, applying the principle of least privilege for job management.
Approach. The correct built-in database role to assign is SQLAgentUserRole, defined in the msdb database. SQLAgentUserRole is the least privileged of the three SQL Server Agent fixed database roles (SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole). Members of SQLAgentUserRole can create jobs, view only their own jobs, and modify/delete/disable only the jobs they own - which exactly matches the requirements. The role is defined in the msdb database (not the user database DB1), because SQL Server Agent stores all job metadata in msdb. Assigning SQLAgentReaderRole or SQLAgentOperatorRole would grant more permissions than needed (e.g., viewing or modifying ALL jobs), violating least privilege.
Concept tested. SQL Server Agent fixed database roles (SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole) defined in the msdb database, and the principle of least privilege when granting job management permissions on SQL Server on Azure Virtual Machines.
Reference. https://learn.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles
Topics
Community Discussion
No community discussion yet for this question.