Through the Dynamic Data Masking feature in SQL Server, we can hide the sensitive data by masking the data from the user who does not have permissions. (Here the data in the database is not changed).
There are 4 different functions to do that –
- default – Entire column is masked.
- partial – only works with string, for masking staring and / or ending characters of the column data.
- email – shows only the first character of the column data and masks the rest.
- random – only works with numeric, the column data is replaced by random values.
- e.g. Create Table
- e.g. Alter Table
- To find the masking details applied on columns –
- Mask permissions –
- Unmask and mask permission –
Granting UNMASK permission to the user allows to see the unmasked data.
- For Azure SQL Database, we can enable and specify masking through the interface itself, select the Dynamic Data Masking option for the table, click on the Add mask button
Apply the masking format as needed.
Reference – Pluralsight – SQL Server Course
Hope it helps..