How to grant unmask permission to a user for one particular table and to one particular column within a table?
For example, if in table1 if column1 and column2 is masked and in table2 if column3 and column4 is masked, for user usr1 with select Access if i want to provide unmask only to table1 and only column1 how to do so?
Unfortunately, there is no granularity for the UNMASK permission in SQL Server, users either see all masked data as masked or unmasked depending on whether they have UNMASK granted.
You could create a low-privilege user that has SELECT and UNMASK and create a stored procedure to retrieve only the columns you're after into a temporary table while impersonating this user. These results can then be joined back to your masked table after reverting the impersonation so that the columns that should be masked are and those that should be unmasked are retrieved from the temp table.
It is stated that in SQL Server 2022, UNMASK permission at the database-level, schema-level, table-level or at the column-level to a user or database role can be granted and revoked. just wanted to share. https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver16#granular
