Answer-first summary for fast verification
Answer: From Microsoft SQL Server Management Studio, set an email mask on the Email column.
## Analysis of Dynamic Data Masking Options for Azure Synapse SQL Pool ### Understanding the Requirement The scenario requires implementing **Dynamic Data Masking (DDM)** on the `Email` column in an Azure Synapse Analytics dedicated SQL pool to display email addresses in the format `aXXX@XXXX.com` for non-administrative users. ### Evaluation of Options **✅ Option A: From Microsoft SQL Server Management Studio, set an email mask on the Email column** - **Optimal Choice**: This is the most direct and reliable method for Azure Synapse dedicated SQL pools - **Technical Implementation**: Using T-SQL commands like: ```sql ALTER TABLE dbo.Customers ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()'); ``` - **Advantages**: - Direct control over masking configuration - Works consistently across all Azure SQL services including Synapse dedicated SQL pools - The `email()` masking function specifically produces the required `aXXX@XXXX.com` format - Can be automated and version-controlled **❌ Option B: From the Azure portal, set a mask on the Email column** - **Limitations**: - Azure Synapse dedicated SQL pools do not support Dynamic Data Masking configuration through the Azure portal UI - Portal-based DDM is primarily available for Azure SQL Database, not Synapse dedicated SQL pools - Even if available, the default email masking might not match the exact required format **❌ Option C: From Microsoft SQL Server Management Studio, grant SELECT permission excluding Email** - **Inappropriate**: This approach would completely hide the Email column rather than masking it - **Violates Requirements**: Users need to see masked email addresses, not have the column entirely hidden - **Poor User Experience**: Would break applications expecting the Email column to be present **❌ Option D: From the Azure portal, set a sensitivity classification** - **Wrong Solution**: Sensitivity classification helps identify and label sensitive data but does not implement data masking - **No Masking Functionality**: This only adds metadata tags and does not alter how data is displayed to users ### Key Technical Considerations - **Azure Synapse dedicated SQL pools** primarily support Dynamic Data Masking configuration through T-SQL commands - The built-in `email()` masking function in SQL Server/Azure SQL automatically produces the `aXXX@XXXX.com` format - Portal-based configuration is not consistently available across all Azure SQL services - T-SQL approach provides the most reliable and service-agnostic solution ### Conclusion Option A is the correct approach as it directly addresses the requirement using the appropriate tool (SSMS with T-SQL) and the built-in email masking function that produces the exact format specified in the requirement.
Ultimate access to all questions.
Author: LeetQuiz Editorial Team
You have a SQL pool in Azure Synapse Analytics containing a table named dbo.Customers with an Email column. You need to ensure non-administrative users cannot view full email addresses and instead see values formatted as aXXX@XXXX.com. What should you do?
A
From Microsoft SQL Server Management Studio, set an email mask on the Email column.
B
From the Azure portal, set a mask on the Email column.
C
From Microsoft SQL Server Management Studio, grant the SELECT permission to the users for all the columns in the dbo.Customers table except Email.
D
From the Azure portal, set a sensitivity classification of Confidential for the Email column.
No comments yet.