Checking if any database objects are encrypted

Question

Checking if any database objects are encrypted

Animal Magic

SSChampion

Points: 13964
More actions
May 2, 2013 at 3:51 am

#273538

Hi,
I have just been given a database to check over to see if we can get at all the data. The database has restored fine, so that's step 1 complete as there is no backup encryption.
Step 2 was to check the objects. I ran the following query
SELECT SCHEMA_NAME(sp.schema_id) AS [Schema],
sp.name AS [Name],
sp.object_id AS [ID],
sp.create_date AS [CreateDate],
sp.modify_date AS [DateLastModified],
CAST(CASE WHEN smsp.definition IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted]
FROM sys.all_objects sp LEFT JOIN sys.sql_modules smsp
ON smsp.object_id = sp.object_id
WHERE smsp.definition IS NULL -- This identifies an encrypted object
AND sp.type IN ('FN', 'IF', 'V', 'TR', 'PC', 'TF', 'P')
AND sp.is_ms_shipped = 0
SELECT sp.type, sp.type_desc
, COUNT(smsp.definition) AS UnencryptedObjects -- only non-null or unencrypted objects will be counted
, COUNT(*)-COUNT(smsp.definition) AS EncryptedObjects
, COUNT(*) AS Total
FROM sys.all_objects sp LEFT JOIN sys.sql_modules smsp
ON smsp.object_id = sp.object_id
WHERE sp.type IN ('FN', 'IF', 'V', 'TR', 'PC', 'TF', 'P')
AND sp.is_ms_shipped = 0
GROUP BY sp.type, sp.type_desc
....which returned 0 encrypted objects.
I noticed that tables are not included in the above query as they do not have a record in sys.sql_modules.
What checks, if any, do i need to run to check for encrypted data in tables without having to do a select top 1 from each table.
Regards
John

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can look for varbinary columns. It's not a perfect indicator as other things can require varbinary (eg files), but encrypted data must go into varbinary columns.

There's no specific flags for encrypted columns (and selecting from the tables won't necessarily tell you they're encrypted), because encryption is not a table/column attribute, it's just something you do to data before inserting it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Animal Magic SSChampion Points: 13964 More actions · Answer 2

Cheers Gail. I did that check and there were only 4 in the whole DB (user tables only) and i can see all the data in those.

Thanks

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Encryption doesn't stop you from seeing the data, just that you'll see binary values (0xA45B2DF999212...) rather than something you can understand

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Animal Magic SSChampion Points: 13964 More actions · Answer 4

Sure, I meant that but wasn't clear in my response. 🙂

John