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
I found my issue, I had to GRANT Select on the table for the user.
GRANT SELECT ON [dbo].[Test] TO [TestUser];