Anonymize relational data Sql Scripts

Sometimes we need to anonymize sensitive data that was used as an id of rows - consider the following tables:

create table Person(
social_security_number int,
name varchar(50)
)

create table transactions(
    person_social_security_number int,
    month int,
    amount decimal
)

you can find a script to create this test data on [github] (https://gist.github.com/noam-honig/1f83d152b31b827272bb29d400a6ff85)

The social_security_number is sensitive and needs to be anonymized but it's also used in the transactions table, and the relation needs to be maintained.

1. Create a temp table with the original value, and an anonymized newId

   select social_security_number as old_Id, ROW_NUMBER() OVER (order by name) as new_Id 
   into id_map
   from Person
   

2. Update the original table with the generated new_id

   update Person set social_security_number = new_id 
   from id_map where social_security_number = old_id
   

3. Update any other table that uses the social_security_number column

   update transactions set person_social_security_number = new_id 
   from id_map where person_social_security_number = old_id
   

This process will anonymize the social_security_number while still maintaining the cross table relationships

You can find more relevant SQL scripts at https://gist.github.com/Tyriar/d3635c6b6e32ac406623