Plan and implement data platform resources →mediumMultiple Choice

DP-300 Plan and implement data platform resources Practice Question

You are configuring security for an Azure SQL Database that will be accessed by multiple applications. Each application uses a separate service principal managed in Microsoft Entra ID. You need to ensure that each service principal has the minimum required permissions to access only its own set of tables. What should you implement?

⚠ Common exam trap

Candidates often confuse server-level logins with contained database users for Microsoft Entra ID principals, or mistakenly think that broad roles like db_datareader satisfy the 'minimum required permissions' requirement when the question explicitly demands table-level scoping.

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 contained database user for each service principal and grant SELECT, INSERT, UPDATE, DELETE on specific tables.

It creates a contained database user for each service principal (mapped to the Microsoft Entra ID identity) and grants only the specific table-level permissions (SELECT, INSERT, UPDATE, DELETE) required for that application. This follows the principle of least privilege by avoiding broad database roles and ensuring each service principal can only access its own set of tables.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✗
Create a contained database user for each service principal and grant the db_owner role.
Why it's wrong here
db_owner grants full control of the entire database, violating least privilege for table-scoped access. It is tempting as a quick way to guarantee connectivity, and would be correct for a service principal that genuinely administers the whole database.
✓
Create a contained database user for each service principal and grant SELECT, INSERT, UPDATE, DELETE on specific tables.
Why this is correct
Contained database users authenticate directly against Microsoft Entra ID at the database layer, so each service principal maps to its own user. Granting table-scoped DML rights enforces least privilege, satisfying the requirement that each application reaches only its own tables.
✗
Create a server-level login for each service principal and assign db_datareader role in the database.
Why it's wrong here
Server-level logins with db_datareader grant read access to every table in the database, not only each application's own set. It is tempting because logins and database roles are the standard way to grant service principals access, and would be correct if all applications needed identical read-only permissions across the whole database.
✗
Configure a server-level firewall rule for each service principal IP address.
Why it's wrong here
A server-level firewall rule controls network access by IP address, not data-plane permissions within the database. It cannot restrict a service principal to specific tables; it only allows or denies connections from a given IP range. This option is tempting because firewall rules are a common first line of defence for Azure SQL Database, and they would be correct if the requirement were to limit network-level access per application, rather than to enforce row- or table-level authorisation inside the database.

Go deeper

DP-300 Plan and implement data platform resources Practice Question

Why each option matters

Option-by-option breakdown

Related to this question