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?

2 Answers 2


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.