asked
Change all GUIDs in a database
I need a smart script to change all GUIDS in a database, both the constraints and foreign keys. The reason is for GDPR which needs unidentified persondata and no one should be able to reverse personal information matched to a productioncopy. If I just change name and personnumber then a DBA can still restore a productioncopy and join between the databsaes on the GUID and get the real person. It is many tables with millions of rows that needs to be converted. Anyone got a good example? I tried to create a cursor that put all GUID columns in all tables into a temporary table, then loop this and select all distinct guids and make newID() and update everywhere. But oit was not a quick operation. Is it even possible to do this? Best Regards and a happy new year! Pelle
@PerWidell - The below is not an answer to your question, but a suggestion. On first glance, I think changing GUIDs may not be a good idea. While solving bugs, the analysis usually boils down to an identifier, which will be a GUID in your case. Therefore changing GUIDs can make debugging difficult. Now, your question will have solutions no doubt, but they will be complex. To mask the person-data, what you do is perfect. But then the problem here is production access. Here, you can chose to run the change name and person data script every time the production copy is restored. You can also monitor production access by not giving direct access to anyone, but by a more controlled access methodology available; example - the developer will raise a production read request stating the need and the DBA will then approve it if the need seems fine.
Thanks for your suggestion, but as I wrote the solution is still reversible if you can match the guid between productiondata and masked data. So se need a way to unmask all guids as well. We are aware of the risks but debugging use a special person-data (socialsecuritynumber) cause the guids are only used for all datarelations. Cause the relations it is a risk to replace the guid.
17 People are following this question.
