SQL Server Masking not working - Does work on Temp DB

Question

When I run the below code on a Temp database it does work, but not when I run it on a normal table. Very weird and not sure what I am missing.

CREATE TABLE #Test (Username VARCHAR(20))

insert into #Test (Username)
values ( 'Graham' ),( 'Bill'),( 'Ray')

ALTER TABLE #Test ALTER COLUMN [Username]    VARCHAR(50) MASKED WITH (FUNCTION = 'partial(2,"XXX",0)');

Anyone know what is going on?

I did do everything correct based on this link. https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-2017

Whats the compatibility level of your database where you are trying to use this? — Dale K, Mar 19 '19 at 04:00
You mean between TempDB and the actual database of my table dbo.Test? — Etienne, Mar 19 '19 at 04:08
It's 130, you think that might have something to do with it? — Etienne, Mar 19 '19 at 05:00
Well it could have been - I believe that feature turned up in SQL Server 2016 - which is compatibility level 130 - so it should be working. — Dale K, Mar 19 '19 at 05:09

score 0 · Accepted Answer · answered Mar 19 '19 at 06:26

0

I found my issue, I had to GRANT Select on the table for the user.

GRANT SELECT ON [dbo].[Test] TO [TestUser];

answered Mar 19 '19 at 06:26

Etienne

7,141
42
108
160

SQL Server Masking not working - Does work on Temp DB

1 Answers1