SQL Server Data Masking :
Masking data can be done through updating the confidential information in the database like SSN , Password,Username etc with some other values.
To mask the entire data quickly , its good to create table with list of the tables and columns which you need mask and run the generic statements to generate the update scripts on the table.
But updating the primary key value is difficult task as we need to update the foreign key references as well.
Below procedure will update the primary key and all the related foreign keys.
Note: this procedure will not mask the table references where the PK is identity
Mostly we don't need to mask the identity values as all are auto generated values.
Steps involved in the below procedure :
- Add an identity column to the primary key table
- check the primary key datatype if its character datatype then it will append 'MASK' to the value its going to update
- drop all the foreign keys
- update all the foreign keys with the primary key table newly added identity columns value while referring the primary key value in the primary key table
- update the primary key value with the newly added identity column
- adds all the foreign keys which are dropped before in step:3
- drop the newly added identity column in the primary key table which is added in step :1