How can I create multiple users with different masking types using Dynamic Data Masking in SQL?

Question

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.

Answer 1

You cannot have different masking types for different users. The mask is on the column. The user, or more probably the role, can have the UNMASK permission to see data in all columns regardless of the mask. Dynamic Data Masking is not a security feature.