SQL 2017 - Granting unmask access to security group not working getting errors

Kevin 0

Hi All,

I am trying to grant unmask access for a security group for a specific table and for a specific column however im getting a the below error message.

query executed.

GRANT UNMASK ON schemaName.tablename(ColumnName) TO [Account that requires access, in my case a security group]

error message :

Msg 102, Level 15, State 1, Line 0

Incorrect syntax near 'UNMASK'.

Anyone experience this issue and how does one resolve this?

Regards

MikeyQiao-MSFT 560 Reputation points Microsoft Vendor

2024-04-19T01:29:18.52+00:00

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

2 answers

Olaf Helper 40,896 Reputation points

2024-04-15T16:30:06.1566667+00:00

See

https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-ver16

=> example G. Grant UNMASK permission on a column for the correct syntax.
Please sign in to rate this answer.
Kevin 0 Reputation points

2024-04-16T06:48:09.4633333+00:00

Hi Olaf,

I used the below and still get the error in MS SQL 2017. This script does work on SQL 2022 but not SQL 2017. Is there something different in the script that i need to change so that it works on SQL 2017?

GRANT UNMASK ON OBJECT::schemaName.tablename (ColumnName) to [Account that requires access, in my case a security group];

GO

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near 'UNMASK'.

Thanks for your help so far

Regards

Kevin 0 Reputation points

2024-04-16T08:57:53.7366667+00:00

Hi,

I found out its a SQL version restriction SQL 2017 cant do column level unmask. How does one grant unmask on a table level for a account ?

SQL version is SQL 2017

Script used

GRANT UNMASK ON schemaName.tablenameto [Account that requires access, in my case a security group];

error im still getting

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near 'UNMASK'.

Regards
Sign in to comment
MikeyQiao-MSFT 560 Reputation points Microsoft Vendor

2024-04-16T08:12:38.8666667+00:00
Hi，Kevin

Starting from SQL Server 2022, dynamic data masking supports granting UNMASK permission at the column level.

Details refer to https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver16#granular

So in SQL Server 2017, you cannot grant UNMASK permission on a specific column.

The executable statement is as follows:

GRANT UNMASK TO [UserOrGroupName];
Please sign in to rate this answer.
Kevin 0 Reputation points

2024-04-16T09:00:41.2333333+00:00

Hi,

I found out its a SQL version restriction SQL 2017 cant do column level unmask. How does one grant unmask on a table level for a account ?

SQL version is SQL 2017

Script used

GRANT UNMASK ON schemaName.tablenameto [Account that requires access, in my case a security group];

error im still getting

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near 'UNMASK'.

Regards

MikeyQiao-MSFT 560 Reputation points Microsoft Vendor

2024-04-16T09:30:50.4133333+00:00

Unfortunately

GRANT UNMASK does not support usage on specific schemas or tables in sql server 2017.

Once a user is granted UNMASK permission, the dynamic masking feature of the entire database becomes ineffective for them (provided they have SELECT permissions on other tables).

So you can only execue this statement：

GRANT UNMASK TO [UserOrGroupName];

Users with UNMASK permission can see unmasked data. If you do not want a certain account to see the raw data, make sure not to give that account UNMASK permission.

So if you must use it this way, please be very careful about that user's select permissions on other tables with dynamic masking.

You may consider the following alternatives:

Create a view that selects the specific table and columns for which you want the user to see the raw data. Then, you can grant the user SELECT permission on this view. For example:

CREATE VIEW UnmaskedView AS SELECT Column1, Column2, UnmaskedColumn FROM YourTable; GRANT SELECT ON UnmaskedView TO [AccountName];
Sign in to comment