0

I need to anonymize some data from our production server so that I can review the data for further business opportunities. For example, I use the Adventureworks database.

Let's say I have the data as in the table below and I have to anonymize it all.

enter image description here

It would be good to have names in the right form and not some gibberish like Axpsr Raqnclo. The account ID could be anything just numbers or combination of numbers and letters.

Is there any good solution to this or some tool for this? I know that for account ID I can use function RAND() in SQL but no idea how to do the rest. I'm using SQL Server with SSIS and SSAS.

Any ideas please?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Roman Schmidt
  • 55
  • 1
  • 12
  • 1
    Either write the update statement yourself, based on your schema and requirements, or use a 3rd party tool (out of scope for SO) – Nick.Mc May 20 '21 at 09:49
  • 1
    some answers here : https://dba.stackexchange.com/questions/23786/data-obfuscation-in-sql-server – DonKnacki May 20 '21 at 09:50
  • 1
    *"It would be good to have names in the right form and not some gibberish like Axpsr Raqnclo"* If you want realistic, but anonymised data then most likely you are going to want a 3rd party tool, but like @Nick.McDermaid said, that'll off topic for [so]. A search for them on your favourite search engine will certainly find you some, but I would suggest that the *good* ones do not come cheap. – Thom A May 20 '21 at 10:20
  • 1
    Depends a bit how strict the anonymization must be. I once created a view over original patients data in a hospital for a day of open doors where I just took random record ids between 100000 and 200000 (of some old records) so e.g. the first name came from record 173881 and the last name from 108332 and the town from 193422. When a given name or surname contained a space or a dash sign I would not use it and go for another random record as that could have been identifiable. – Christoph May 20 '21 at 23:17
  • Thanks to everyone for the advice ! I asked a silly question, because deep in my head I know that these are just these two answers - write a long statement myself or I will use some third tool (as Larnu mentioned the good ones, of course they are not cheap) but I needed to confirm that. Thanks to all again ! – Roman Schmidt May 21 '21 at 06:13

0 Answers0