Quick Script: Finding Columns

Locked Lock, Tom Magliery, http://www.flickr.com/photos/mag3737/1420554128/
Is this secured enough?

For any data professional, data security should be an utmost priority.  Having unsecured data lying about can cause major issues, if discovered.  I’m speaking of software security, more specifically data encryption or data masking.

Finding the instances of unsecured data in an environment can be a pain.  Last year, I had to look for unencrypted data within a non-production environment.  In this case, I was looking for Social Security or also known as Tax Identification Numbers.

Red-Gate offers a tool, SQL Search, which is completely free and completely awesome. I’ve used this tool previously on many occasions .   The problem that I had was that I wanted to look across all servers within an environment utilizing a Central Management Server.  I haven’t been able to find a way to accomplish that with SQL Search so I set out to create my own script.

There are some things to keep in mind about this script,

  1. It makes assumptions that the column is named in a certain pattern.
  2. It uses dynamic SQL.
  3. It uses a WHILE loop.

However, even with these nuances,  the script works pretty well to help find potential sensitive data that might not be encrypted across multiple servers.   Of course, I’m also assuming that encryption is intended to be handled by the database and not some other process.

DISCLAIMER:  While I’ve been able to run it across multiple servers, over several hundred databases without an issue, your mileage will vary.  Please test accordingly.   NEVER run code that you find on the internet against your Production systems without testing first.  You have been warned!

The script will return a number of things:

  • Table Name
  • Schema Name
  • Column Name
  • Column Data Type
  • Size of the Column
  • Number of rows

Using this script in conjunction with a Central Management Server, I’ve been able to find out where potential sensitive data might be residing.  I used this list of columns to investigate whether or not it contained sensitive data.  If it did, I would either mask it or encrypt it, depending on our company policies.

I haven’t run into any issues with running the script in Production, however, I would again advise caution.  Your mileage will vary.  Test accordingly!


© 2015, John Morehouse. All rights reserved.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.