SQL server - Masking / changing value uniqueidentifier before inserting

Question

So the main goal is to mask or randomly change value of uniqueidentifier before inserting.

For example: I have a table

Create table Student
(
Student_ID masked WITH (FUNCTION='default()') DEFAULT NEWID()
Student_Name varchar(100),
)

Im inserting new data:

insert into DDM_Student_Sample values ('B9BC5E61-0F3C-498F-AF2C-1AC16446A846','Stuart Little Joe')

The result is the same output as it was inserted, i.e. uniqueidentifier = 'B9BC5E61-0F3C-498F-AF2C-1AC16446A846'

I have created a user with login:

CREATE USER MAIN_USER FOR LOGIN MAIN_USER; 
EXEC sp_addrolemember 'db_owner', 'MAIN_USER';

By toturial (http://www.sqlservercentral.com/articles/Security/149689/) it says you must create user without login. Maybe thats the problem why Student_ID does not change randomly.

In the end, maybe there is another way before inserting data to table change it value randomly. Or I should just change value from front-end? And write something like :

model.StudentId = Guid.NewGuid();
model.SaveToDatabase();

Answer 1

Dynamic Sql Server Data Masking is designed to prevent accidental exposure of sensitive data. It doesn't change the actual stored value. It just applies the mask to values before showing the data. I think that's why it is called dynamic

The result is the same output as it was inserted

I guess, you see the actual value because you have UNMASK permission or CONTROL permission (which includes UNMASK). Moreover user without UNMASK permission can "guess" the actual value with simple select

select from Student where Student_ID = 'B9BC5E61-0F3C-498F-AF2C-1AC16446A846'

It will show the row with masked value.

maybe there is another way before inserting data to table change it value randomly

It depends on your needs. If you need to restore data you can use column encrypting. If you just need to uglify data you can go with a regular DB trigger or just change value from front-end as you mentioned.