Hotspot Question You have an Azure subscription that contains a group named Group1 and an Azure SQL managed instance that hosts a database named DB1. You need to ensure that Group1 has read access…

Question

Exhibit

Answer Area

• GRANT permissionSELECT
  DELETEINSERTSELECTUPDATE
• GRANT securable typeTable
  DatabaseSchemaTable

Explanation

This question tests knowledge of granting database-level permissions to Azure AD groups in Azure SQL Managed Instance using T-SQL, specifically using ALTER DEFAULT PRIVILEGES or database role/schema-level grants to ensure future tables are covered with least privilege.

Approach. The correct T-SQL statement should use 'ALTER ROLE db_datareader ADD MEMBER [Group1]' after creating a user from the Azure AD group, OR use schema-level default grants. The most precise least-privilege approach for ensuring read access to NEW (future) tables is to grant SELECT on the SCHEMA to the group: first execute 'CREATE USER [Group1] FROM EXTERNAL PROVIDER;' to create the Azure AD group as a database user, then execute 'ALTER ROLE db_datareader ADD MEMBER [Group1];' - the db_datareader role grants SELECT on all current AND future tables in the database, satisfying the requirement. This avoids granting broader roles like db_owner or db_ddladmin, adhering to least privilege. The key fill-in selections would be: the role name 'db_datareader' (not db_datawriter or db_owner) and the member '[Group1]' mapped as EXTERNAL PROVIDER.

Concept tested. Granting least-privilege read access to Azure AD groups on Azure SQL Managed Instance using T-SQL - specifically creating an external provider user from an Azure AD group and adding it to the db_datareader fixed database role so that SELECT access applies to all current and future tables without over-provisioning permissions.

Reference. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/aad-security-configure-tutorial and https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles

No community discussion yet for this question.