When I try to create new users with different masking types, the masking type of the last user I created is constantly defined for all users, how can I create this correctly. This is how I do the user creation:
CREATE LOGIN test1
WITH PASSWORD = '123' create user test1 for login test1
ALTER ROLE db_datareader
ADD MEMBER[test1]
GRANT SELECT TO test1 ALTER TABLE[user] ALTER COLUMN id int MASKED WITH(FUNCTION = 'random(1, 100000)');
ALTER TABLE[user] ALTER COLUMN name varchar(max) MASKED WITH(FUNCTION = 'partial(1, "xxxxx" ,1)');
ALTER TABLE[user] ALTER COLUMN surname varchar(max) MASKED WITH(FUNCTION = 'partial(1, "xxxxx" ,1)');
ALTER TABLE[user] ALTER COLUMN age varchar(2) MASKED WITH(FUNCTION = 'partial(1, "xxxxx" ,1)');
ALTER TABLE[user] ALTER COLUMN tckn varchar(11) MASKED WITH(FUNCTION = 'partial(1, "xxxxx", 1)');
ALTER TABLE[user] ALTER COLUMN phone_number varchar(max) MASKED WITH(FUNCTION = 'partial(1, "xxxxx" ,1)');
ALTER TABLE[user] ALTER COLUMN address varchar(max) MASKED WITH(FUNCTION = 'partial(1, "xxxxx" ,1)');
ALTER TABLE[user] ALTER COLUMN isdeleted bit MASKED WITH(FUNCTION = 'random(0, 1)')
This way I define excluding masking with first and last character to user test1. As a result of this definition, the data for the test1 user looks like this:
When I log in as test1 user and query, here is the data
Then when I create a user with a different masking type, for example like this:
CREATE LOGIN test2
WITH PASSWORD = '123' create user test2 for login test2
ALTER ROLE db_datareader
ADD MEMBER[test2]
GRANT SELECT TO test2 ALTER TABLE[user] ALTER COLUMN id int MASKED WITH(FUNCTION = 'random(1, 100000)');
ALTER TABLE[user] ALTER COLUMN name varchar(max) MASKED WITH(FUNCTION = 'default()');
ALTER TABLE[user] ALTER COLUMN surname varchar(max) MASKED WITH(FUNCTION = 'default()');
ALTER TABLE[user] ALTER COLUMN age varchar(2) MASKED WITH(FUNCTION = 'default()');
ALTER TABLE[user] ALTER COLUMN tckn varchar(11) MASKED WITH(FUNCTION = 'default()');
ALTER TABLE[user] ALTER COLUMN phone_number varchar(max) MASKED WITH(FUNCTION = 'default()');
ALTER TABLE[user] ALTER COLUMN address varchar(max) MASKED WITH(FUNCTION = 'default()');
ALTER TABLE[user] ALTER COLUMN isdeleted bit MASKED WITH(FUNCTION = 'random(0, 1)');
In this way, I define the test 2 user who will see all the characters as masked. After this definition, the data for the test2 user looks like this:
When I log in as test2 user and query, here is the data
There doesn't seem to be any problem so far. But when I query again from test1 user, the data comes to test2 user with the masking I defined. It also looks like this:
When I go back to user test1 and query again, the data that comes in
In this way, whenever I define a new user with a different masking type, the masking types of the other users are also the masking type of the last defined user. How can I fix this problem. Thank you very much if you help.
