Scrubbing Names via SQL Query/Batch

Question

We have sensitive information (names of people) and we're looking to put into place a script to "scrub" of the names. Has anybody come up with a good algorithm to do this, something other than just making all of the last names "TEST" or something?

Thanks.

Answer 1

I had to do this for NPI (non-public information) reasons at a previous employer. Ended up scrambling fields in a script, since a first name / last name pair isn't NPI unless it's tied to something else.

Here's a rough version of the script. Season to taste. Also, this assumes you have first and last names in separate fields.

CREATE TABLE #RandomData
(
    DataID int not null,
    ID int not null,
    FirstName varchar(50),
    LastName varchar(50)
)

INSERT INTO #RandomData (ID, DataID)
SELECT ROW_NUMBER() OVER (ORDER BY NEWID()), DataId FROM DataTable

UPDATE r SET r.FirstName = x.FirstName
FROM #RandomData r
INNER JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ID, FirstName FROM DataTable
) x
    ON x.ID = r.ID

UPDATE r SET r.LastName = x.LastName
FROM #RandomData r
INNER JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ID, LastName FROM DataTable
) x
    ON x.ID = r.ID

CREATE CLUSTERED INDEX PK_RandomData ON #RandomData
(
    DataID asc
)

ALTER TABLE DataTable DISABLE TRIGGER ALL

UPDATE dt 
    SET dt.FirstName = r.FirstName, 
    dt.LastName = r.LastName
FROM DataTable dt
INNER JOIN #RandomData r
    ON dt.DataId = r.DataId

ALTER TABLE DataTable ENABLE TRIGGER ALL

DROP TABLE #RandomData

Answer 2

If you don't want to know what the name is but for whatever reason you want to keep something in the table for that record, you can always just use a hash of the name:

UPDATE MyTable
SET Name = HASHBYTES('SHA1', Name)

This will keep a unique value for each name record, but you will have no way to know what the name is. You can, however, see if a name matches a value by comparing an hashed input to the stored hash.

Cryptographic hashes like this are one way, so you can create an identical hash from an identical input string, but not determine the input string even if you know what the hash is.