scramble the value of one column and unscramble it?

I want to change all the values in one column, as it is a production database and I don't want to give the data to testers without obfuscation.

Example:

Name             Name
----     -->     ----
ABC              BCD
PQR              QRS

I have searched scrambling and other things but my issue is I want to change the obfuscated data back to their original values when needed.

I need a generic script so I can apply it to an arbitrary table if required.

There are plenty of generic data masking script, but the only problem is that no one understands your data better than you.

You have to write your own masking script based on what data you consider sensitive.

When you do a backup/restore of PROD data on UAT/QC environments for testing, its advisable to mask the data with dummy data. Masking of data can be done through updating the confidential information in the database like SSN , Password,Username etc with some other values.

You can even log what data is going to change in tables with different schema. For e.g

If person.Employee table is going to be masked, then the before value can be written to maskPerson.Employee table. This way, you will have before values as well. (dont really need this as in UAT or QC you dont care of the before value, which is PROD value you are masking).

Some good references :

• Data Masking Script

• Obfuscating your SQL Server Data

• Data Generator for SQL SERVER 2000,2005 and MSDE

I found this connect item wherein Microsoft is going to provide a data masking feature in future release ... (still could not make it for 2012).

StackExchange Database Administrators Q#47967, answer score: 3