Masking data is the usual way to protect sensitive information outside production, in development and test copies. Which approach to use depends on what you need:

1. Data Masking Techniques

These are used to hide sensitive data while maintaining its format and usability.

Static Data Masking

• Done once and stored in the database.
• Example: Replace real names with fake ones.

UPDATE Customers
SET Name = CONCAT('User', CustomerID),
   Email = CONCAT('user', CustomerID, '@example.com');

Dynamic Data Masking (SQL Server feature)

• Masks data at query time without changing the actual data.
• Example:

ALTER TABLE Customers
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

2. Data Substitution

Replace real data with realistic but fake data.

• Use lookup tables or random generators.
• Example:

UPDATE Employees
SET SSN = RIGHT(CONVERT(VARCHAR, NEWID()), 9);

3. Encryption

Encrypt sensitive columns using built-in SQL Server functions.

-- Create a symmetric key
CREATE SYMMETRIC KEY TestKey WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = '<your password here>';
-- Encrypt data
OPEN SYMMETRIC KEY TestKey DECRYPTION BY PASSWORD = '<your password here>';
UPDATE Customers
SET CreditCardNumber = ENCRYPTBYKEY(KEY_GUID('TestKey'), CreditCardNumber);

4. Use of External Tools

There are tools that automate data obfuscation:

• Redgate Data Masker
• Informatica
• Microsoft SQL Server Data Masking
• Delphix

Best Practices

• Always obfuscate in a non-production environment.
• Make sure obfuscated data maintains referential integrity.
• Document your masking logic for audit and compliance.
• Use role-based access control to limit who can see real vs. masked data.

From my SQL Server DBA handbook.