sql-servermaskingtempdb

SQL Server Masking not working - Does work on Temp DB

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

Solution

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

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