SQLTeam.com | Weblogs | Forums

Do you see anything wrong with this case statement?


#1

[
SELECT DB_NAME(DATABASE_ID),
CASE
WHEN e.encryption_state = 0 THEN 'No database encryption key present, no encryption'
WHEN e.encryption_state = 1 THEN 'Unencrypted'
WHEN e.encryption_state = 2 THEN 'Encryption in progress'
WHEN e.encryption_state = 3 THEN 'Encrypted'
WHEN e.encryption_state = 4 THEN 'ey change in progress'
WHEN e.encryption_state = 4 THEN 'Decryption in progress'
WHEN e.encryption_state = 4 THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
END [ENCRYPTED_STATE]
FROM sys.dm_database_encryption_keys e

select NAME, is_encrypted from sys.databases](http://Code)


#2

Nothing wrong as far as syntax goes.

As far as logic, the 3 sets of WHEN expressions for e.encryption_state = 4 seems illogical. the second and third instances of e.encryption_state = 4 will never be executed. Perhaps you meant them to be 5 and 6 rather than repeated instances of 4?


#3

I like to put an ELSE in my CASE statements ... it's optional though.


#4

You're correct. The sequence for the output should be 4,5.6. That's a typo.

The issue is this. There is ONE user db on this box (CTRM). This case expression "should" be looking at all the db's on the box, not just the system db's, and showing the encryption state for each. However, the resultset only returns a report for Tempdb, and not the CTRM db that is on the box. When i run

SELECT encryption_state
FROM sys.dm_database_encryption_keys;
GO

I get a resultset showing encryption state for ALL of the db's on the box.

Why is the Case Expression NOT showing me the same resultset?


#5

If you get a row for every database for the following query, then you will get a row for every database for your original query as well. There is nothing in your case expression that would cause a row to be removed.

SELECT encryption_state
FROM sys.dm_database_encryption_keys;

If some of the databases are not encrypted, what you probably should do is a left join to sys.databases like this:

SELECT
    d.name,
    CASE WHEN e.encryption_state = 0
         THEN 'No database encryption key present, no encryption'
         WHEN e.encryption_state = 1 THEN 'Unencrypted'
         WHEN e.encryption_state = 2 THEN 'Encryption in progress'
         WHEN e.encryption_state = 3 THEN 'Encrypted'
         WHEN e.encryption_state = 4 THEN 'ey change in progress'
         WHEN e.encryption_state = 5 THEN 'Decryption in progress'
         WHEN e.encryption_state = 6
         THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
         ELSE 'No database encryption key present, no encryption'
    END [ENCRYPTED_STATE]
FROM
    sys.databases d
    LEFT OUTER JOIN sys.dm_database_encryption_keys e ON
        d.database_id = e.database_id;

#6

Gracias. That is precisely what was missing.