Monday, October 12, 2009

ms sql server data masking

SQL Server Data Masking :
Download code

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 : 
  1. Add an identity column to the primary key table
  2. check the primary key datatype if its character datatype then it will append 'MASK' to the value its going to update
  3. drop all the foreign keys
  4. 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
  5. update the primary key value with the newly added identity column
  6. adds all the foreign keys which are dropped before in step:3
  7. drop the newly added identity column in the primary key table which is added in step :1 

Post a Comment

Featured Post

SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database. Developed this stored procedure on my labs to simulate the Mi...