Monday, October 12, 2009

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:

Anonymous said...

solved my purpose....
thanks

Arthur said...

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

calsql said...

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

Terry said...

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

Reneesh Prabha said...

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

chandra sekhar said...

thanks prabha. i have updated the link with the right url.

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...

Contributors