Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favourite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved in your SSC briefcase from the favorites tab.
Download now (direct download link)

Mask PII data (Personally Identifiable Information)

By Sylvia Moestl Vasilik,

Privacy has been an increasing concern in information technology, and many companies have policies in place that prevent data that could identify a person from being easily accessed. This data is called PII (Personally Identifiable Information). Laws like HIPAA (Health Insurance Portability and Accountability Act) in particular are restrictive of how PII data can be accessed

If these laws are strictly interpreted, it can cause concerns even with staightforward database tasks, such as making a copy of a production database available for development work. Some companies now forbid this common and useful practice.

Using a script such as ##Common_PIIInfoDelete can diminish these concerns. This script will update all fields identified as PII to a neutral value (such as an empty string).

I've included a sample that shows how to call ##Common_PIIInfoDelete. Before you run it, please note the following:

  • This must, of course, never be run in any production database. There's a validation in the script that checks the server name which should be configured for your environment to prevent this.
  • You'll need to update the values in the ##Settings table to match the fields that you want to mask
  • It currently runs all the updates in one chunk. It could be improved by using the primary key of the table to run in chunks.
if object_id('tempdb..##Settings') is not null drop table ##Settings
Create Table ##Settings (
Table_Schema sysname
,Table_Name sysname
,Column_Name sysname
,MaskValue varchar(50)
-- Now, insert into the ##Settings table. These values will be used when calling the stored procedure
Insert into ##Settings
-- (Table_Schema Table_Name Column_Name MaskValue
select 'dbo' ,'CCDetailLog' ,'FirstName' ,'''''' union all
select 'dbo' ,'CCDetailLog' ,'LastName' ,'''''' union all
select 'dbo' ,'CCDetailLog' ,'Address1' ,'''''' union all
select 'dbo' ,'CCDetailLog' ,'CustomerEmailAddress' ,'''''' union all
select 'dbo' ,'CCDetailLog' ,'CreditCardNbr' ,'convert(varbinary, '''')' union all
select 'Payment' ,'CCDetailLog' ,'PhoneAreaCodeNbr' ,'''''' union all
select 'Payment' ,'CCDetailLog' ,'PhoneLocalNbr' ,'''''' union all
select 'Payment' ,'CCDetailLog' ,'PhoneExtensionNbr' ,'''''' union all
select 'Stage' ,'CCDetailLogStage' ,'LastName' ,'''''' union all
select 'Stage' ,'CCDetailLogStage' ,'Address1' ,'''''' union all
select 'Stage' ,'CCDetailLogStage' ,'CustomerEmailAddress' ,''''''
-- Call the stored procedure to mask the PII values
exec ##Common_PIIInfoDelete

Total article views: 1060 | Views in the last 30 days: 9
Related Articles

working with union all or union

union all vs union


Sql Server - Union and Union All

Union and Union All, both are used to select data from one or more than one tables but still they......





Union All

sum..Union all



One of the less used commands in T-SQL, the UNION command can come in very handy in a number of situ...


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones