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 

--sql server data masking , replace sensitive data with some dummy data GO --PR_UPDATE_REFERENCES 'sales.customer','customerid' GO -- CREATE procedure PR_UPDATE_REFERENCES(@TBL_NAME VARCHAR(256),@PK_COL_NAME VARCHAR(256),@IDENTITY_COL VARCHAR(256)='IID_TEMP',@MASK_WORD VARCHAR(256)='MASK') AS SET NOCOUNT ON -- Adding the Identity Column select 'alter table '+ @TBL_NAME + ' add ' + @IDENTITY_COL +' INT IDENTITY(1,1)' select 'GO' -- If the primary key is numeric data type then the mask word will be '' to avoid the casting errors select @MASK_WORD = '' from sys.columns a join sys.types b on a.system_type_id = b.system_type_id where object_id = object_id(@TBL_NAME) and a.name = @PK_COL_NAME AND b.name in ('tinyint','smallint','int','numeric') -- updating all the foreighn keys 1st with identity column + 'MASK' -- will update the Primary key at the end of the script -- !! update through join with new added identity column select distinct -- Below part will produce the script to drop All Constraints 'ALTER TABLE ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) +' DROP CONSTRAINT ' + object_name(constid) from sys.sysforeignkeys A join syscolumns b on a.fkeyid = b.id and a.fkey = b.colid join syscolumns c on a.rkeyid = c.id and a.rkey = c.colid JOIN sys.objects SOF ON SOF.OBJECT_ID = A.FKEYID where SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) = @TBL_NAME and C.NAME = @PK_COL_NAME --------------------------------------------------------------------------------------------------------- -- Below statement will update all the foreing keys with the values in the updated primary key select + CHAR(13) + 'UPDATE ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) +' SET ' + B.name + ' = CAST('+object_name(rkeyid) + '.'+ @IDENTITY_COL + ' AS VARCHAR(9))' +' + '''+ @MASK_WORD +'''' +' FROM ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid)+' ' + 'JOIN ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) + ' '+ + 'ON ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) +'.' +C.NAME +' = ' +SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) + '.'+ B.NAME from sys.sysforeignkeys A join syscolumns b on a.fkeyid = b.id and a.fkey = b.colid join syscolumns c on a.rkeyid = c.id and a.rkey = c.colid JOIN sys.objects SOF ON SOF.OBJECT_ID = A.FKEYID where SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) = @TBL_NAME and C.NAME = @PK_COL_NAME --------------------------------------------------------------------------------------------------------- -- Below statement will update all the foreing keys with the values in the updated primary key SELECT 'update '+ @TBL_NAME + ' set ' + @PK_COL_NAME + ' = '+@IDENTITY_COL +'+'''+ @MASK_WORD+'''' ------------------------------------------------------------------------------------------------ select + CHAR(13) + -- Below statement will produce the script to Create All Constraints which are Scripted to Dropped Before 'ALTER TABLE ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) +' ADD CONSTRAINT ' + object_name(constid) +' FOREIGN KEY (' + b.name + ') REFERENCES ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) + '(' + c.name + ')' from sys.sysforeignkeys A join syscolumns b on a.fkeyid = b.id and a.fkey = b.colid join syscolumns c on a.rkeyid = c.id and a.rkey = c.colid JOIN sys.objects SOF ON SOF.OBJECT_ID = A.FKEYID where SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) = @TBL_NAME and C.NAME = @PK_COL_NAME ------------------------------------------------------------------------------------------------ -- Dropping the Identity Column select 'GO' select 'alter table '+ @TBL_NAME + ' drop COLUMN ' + @IDENTITY_COL