AZ-500 Secure compute, storage, and databases Practice Question
Your company uses Azure SQL Database with Microsoft Entra ID authentication. You need to restrict a user to only view data from the 'Sales' schema, without granting permissions to other schemas. What should you do?
⚠ Common exam trap
A common mix-up: candidates confuse database-level roles (like db_datareader) with schema-level permissions, mistakenly assuming that adding a user to a read-only role is sufficient, while ignoring that db_datareader grants access to all schemas, not a specific one.
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 user mapped to the Entra ID user and grant SELECT on the Sales schema only.
It directly implements the principle of least privilege by creating a database user mapped to the Microsoft Entra ID user and granting SELECT only on the Sales schema. This ensures the user can view data exclusively within that schema, with no implicit permissions on other schemas. Azure SQL Database supports schema-level permissions, making this a precise 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.
- ✗
Add the user to the db_datareader role in the database.
Why it's wrong here
The db_datareader fixed database role grants SELECT permissions on all user tables and views across every schema in the database. Adding a user to this role would expose not just the Sales schema but also HR, Finance, and any other schema, violating the principle of least privilege. This approach is overly broad and fails to meet the requirement of restricting access specifically to the Sales schema.
- ✗
Use a DENY statement on all other schemas for the user.
Why it's wrong here
Applying DENY on all other schemas is a negative security model that requires enumerating every existing schema and remembering to manage DENY grants for any schema created later. Moreover, DENY can be overridden by a GRANT at a higher scope if permissions are ever granted at the database or server level, creating a confusing and error-prone permission state. This approach is brittle and contradicts the best practice of explicitly granting only the necessary permissions on the target schema.
- ✓
Create a user mapped to the Entra ID user and grant SELECT on the Sales schema only.
Why this is correct
This is the correct approach because Azure SQL Database supports creating a database user mapped directly to a Microsoft Entra ID user (CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER). After creating that mapped user, you can issue a focused GRANT SELECT ON SCHEMA::Sales TO [user], which grants read access solely to objects in the Sales schema. This aligns with least privilege by allowing only the specific schema needed and works natively with Entra ID authentication.
- ✗
Create a contained database user with password and assign to db_datareader.
Why it's wrong here
A contained database user with a password uses SQL authentication, not Microsoft Entra ID, so it cannot represent the company's Entra ID user identity. Even if you assign this user to db_datareader, it would still grant broad read access across all schemas rather than limiting access to Sales. This option also adds password management overhead and is incompatible with the stated requirement of using Microsoft Entra ID for authentication.
Go deeper
Related to this question
About these practice questions
One of 617 original AZ-500 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This AZ-500 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 AZ-500 exam.