Scrubbing sensitive data

I am looking for an automated solution to scrub sensitive data from my prod environment to my DEV and DEVINT environments so that I don't have to write lots of code to get this done. Does anyone know if Data Quality Services and a data cleansing step in SSIS can do the trick for me? Or, does anyone else have any suggestions to scrub my data without having to write TSQL to do it?

Scrubbing sensitive data is a vast topic. You basically need to define as per your company's data protection policy - what is considered PII (Personally Identifiable Information) or what is sensitive that you don't want other people to see ?

SQL Server 2012* does not have any native tools to mask or scrub sensitive data.

In my company, we have developed in-house tools that will mask client sensitive data like email, phone, names, and many more.

If you have to do it on a repeating basis, I would suggest you to take the schema from PROD and then use 3rd party tools like data generator from RedGate to generate test data.

There are several methods that will be useful to mask sensitive data (once you have identified - what is considered sensitive) like :

• Masking Personal Identifiable SQL Server Data

• Obfuscating your SQL Server Data

Brent has written a good blog post on How Do You Mask Data for Secure Testing?

* In SQL Server 2016, Microsoft has introduced Dynamic data Masking


It’s a data protection feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results.

StackExchange Database Administrators Q#78693, answer score: 5