If you develop against SQL Server long enough, you will eventually find production data sitting in a dev or test environment. Real names, real emails, real phone numbers, all on the least protected servers you own. I worked through this with a client not long ago, and it turned out to be a good chance to sort out something that trips a lot of people up: there are two different problems hiding under the word "masking," and they have two different solutions.

Problem one: hiding data from people who query production. Maybe support staff need to look up an order but should not see the customer's full phone number. SQL Server has a built-in feature for this called Dynamic Data Masking. You declare a mask on the column, and users without the right permission see the masked version when they query it:

ALTER TABLE dbo.Customer
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

ALTER TABLE dbo.Customer
ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'partial(0, "xxx-xxx-", 4)');

The important thing to understand is that the real data is still there. Dynamic Data Masking changes what a query returns, not what the table stores. Privileged users see everything, and if you back up that database and restore it to dev, every bit of PII comes along for the ride. It is a presentation layer, and for its intended job it works well.

Problem two: sanitizing copies of the data. This is the dev and test refresh scenario, and it needs a different tool: static masking, where you actually overwrite the sensitive values after the restore. On the project I mentioned, we used Microsoft Purview to help identify which columns held sensitive data, then wrote plain T-SQL string masking to scrub them. Nothing fancy, and that is the point:

UPDATE dbo.Customer
SET FirstName = LEFT(FirstName, 1) + REPLICATE('x', LEN(FirstName) - 1),
    LastName  = LEFT(LastName, 1) + REPLICATE('x', LEN(LastName) - 1),
    Email     = LEFT(Email, 1) + 'xxxxx@example.com',
    Phone     = TRANSLATE(Phone, '123456789', '000000000');

None of those choices are accidental. Keeping the first letter and the original length means the data still looks and sorts like data, so your UI testing stays honest. The emails all land at example.com, a domain the IETF reserved for testing, so nothing you do in dev can ever reach a real inbox. And TRANSLATE zeroes out the phone digits while leaving the dashes and parentheses alone, so formatting code still has something realistic to chew on. One version note: TRANSLATE arrived in SQL Server 2017, so on older instances you would chain REPLACE calls instead.

To be clear, masking the strings in a dev copy is not encryption, and it is not bulletproof anonymization. It is a practical way to make sure a stray screenshot, a shared dev connection, or a lost laptop does not expose your customers. For most teams, that covers the real risk.

Whichever route fits your situation, check your work. After a masking pass I like to query for anything that slipped through; however you structure it, the idea is simple: search for emails not at example.com, digits where digits should not survive, and columns the classification pass may have missed.

Put the scripts in source control, run them as part of every refresh, and start with the columns that would hurt the most if they leaked.