SDU Tools: Find String Within a SQL Server Database (Did you mask your database properly?)

I've mentioned that I work on a lot of financial systems. Masking and trimming the production databases so they can be used in development is important, yet I see many issues with masking.

A few months back, I was working on a SQL Server database from a large financial client, and the database was supposed to have been masked. However, what they missed was that the XML metadata associated with images stored in the database still had the actual client's details. Any developer in the organization could have retrieved private details of all the members of the fund.

This just isn't acceptable.

I decided to add a tool to our free SDU Tools collection that would help with this. It's useful way beyond just checking masking though. It lets you look everywhere in a database for a given string. It checks a wide variety of data types that might contain the value and it automatically checks every column in every table.

So even if you are just wondering where a value is stored in a database, this might help. Here's an example:

The procedure is called FindStringWithinADatabase. It takes 3 parameters. The first is the name of the database (in this case WideWorldImporters), the second is the string to search for (in this case Kayla), and the third is a flag to indicate whether or not the full contents of the actual rows should be displayed as well (in this case no).

For checking masking, you can enter a name that you know is real and find everywhere that it exists within the database. Or if you are just running an application, and see a value and wonder where it's stored, you can use this too.

Here's what happens if you set that last parameter to 1 (to indicate that you want to see the rows):

After the same initial output, all relevant rows from each table in the first list are displayed.

You can see the tool in action here:

You can find more information on our SDU Tools here:

https://sqldownunder.com/sdu-tools

We hope you find it useful.

Leave a Reply

Your email address will not be published.