Is there a quick way to find all columns in SQL Server 2008 R2 that are encrypted/have encrypted data?
I need to nullify the data in all encrypted columns in a development server (according to our business rules). I know most of the columns because we use them regularly, but I want to be thorough and also I want to be able to prove that I've found them all.
I've searched the web, looked in INFORMATION_SCHEMA and checked the DMVs I thought would be useful and also sys.columns and sys.objects — but so far no luck.
Best Answer
Assuming you are talking about data that is encrypted with SQL Server keys, there is way to find these columns.
The
Key_name()
Function will return the name of the key used for the encryption for that particular value and will return NULL if there isn't anything encrypted with a "known" key (3rd party, or simple not encrypted).With that knowlegde we can test every column to see if it contains at least one row which has a varbinary value that returns a key name
functionality of key_name()
results:
results:
How to implement it to find encrypted columns
results: