ms sql server data masking


SQL Server Data Masking :
Download code
DataMasking.sql

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 

6 comments:

  1. solved my purpose....
    thanks

    ReplyDelete
  2. Also can be done through SSIS as in http://geekswithblogs.net/Compudicted/archive/2012/03/01/creating-a-custom-ssis-data-flow-component---an-example.aspx

    ReplyDelete
    Replies
    1. Thanks Arthur. I am still working on this to develop a module with sql scripts.

      Delete
  3. Hi, I just wanted to say that you can use DataVeil Freeware to mask SQL Server databases. It takes care of foreign keys automatically. It's very user-friendly and has nice visualizations. The Freeware is fully featured (nothing crippled, no nag screens, etc!), it never expires. The only limit is a maximum of 500,000 masked values per project. You can download and see a short demo video at www.dataveil.com

    ReplyDelete
  4. DataMasking.sql seems to be not available in the given link

    ReplyDelete
    Replies
    1. thanks prabha. i have updated the link with the right url.

      Delete