DP-300 Practice Question: Monitor, configure, and optimize database resources
You are deploying a new application on Azure SQL Database. The application requires that all connections use a specific login, 'AppUser', with the least privileges necessary. The login should only be able to execute stored procedures in the 'Sales' schema and should not have direct access to underlying tables. What should you do?
⚠ Common exam trap
DP-300 often tests the misconception that granting EXECUTE on a schema automatically grants table access; candidates must understand ownership chaining and that schema-level EXECUTE is sufficient for stored procedure execution without direct table permissions.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Create a database role, grant EXECUTE on the 'Sales' schema to the role, and add 'AppUser' to the role.
Creating a database role, granting EXECUTE on the 'Sales' schema to that role, and adding 'AppUser' to the role follows the principle of least privilege. This allows 'AppUser' to execute stored procedures in the Sales schema without granting direct table access, because stored procedures execute with ownership chaining or elevated permissions. This is the most maintainable and secure approach.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Grant the SELECT permission on the 'Sales' schema to 'AppUser'.
Why it's wrong here
Granting SELECT on the Sales schema gives direct table access, contradicting the requirement that AppUser only execute stored procedures. It is tempting because schema-level grants are tidy, and would be correct where the application queries tables rather than calling procedures.
- ✗
Add 'AppUser' to the db_datareader role.
Why it's wrong here
db_datareader grants SELECT on all user tables, giving direct table access the requirement forbids. It is tempting because it is a quick least-privilege-looking role, and would be correct where the application genuinely needs to read table data directly.
- ✓
Create a database role, grant EXECUTE on the 'Sales' schema to the role, and add 'AppUser' to the role.
Why this is correct
Schema-scoped EXECUTE permission on the Sales schema lets AppUser run stored procedures without SELECT rights on base tables, satisfying least privilege. Ownership chaining means the procedures access tables under the schema owner's permissions, so no direct table grants are needed.
- ✗
Grant the EXECUTE permission on each stored procedure individually to 'AppUser'.
Why it's wrong here
Granting EXECUTE on each stored procedure individually satisfies the requirement for least privilege but fails to meet the scenario’s need for a single permission that covers all future procedures in the 'Sales' schema without manual updates. It is tempting because it is the most granular way to restrict execution rights, and it would be correct if the requirement were to limit access to a fixed, known set of procedures rather than to an entire schema.
Go deeper
Related to this question
Learn chapter
Deploying and Configuring Azure SQL Managed Instance
Key term
Azure SQL Performance Tuning
Azure SQL Performance Tuning is the process of optimizing the speed and efficiency of queries and database operations in Microsoft Azure SQL Database or SQL Managed Instance to reduce latency and improve throughput.
About these practice questions
This DP-300 question is part of Courseiva's 574-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Microsoft exam blueprint
This DP-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-300 exam.