sp_blitz TDE Certificate Not Backed up Recently - check not working if not run from master database

[ghauan]

Version of the script
@Version = '8.14', @VersionDate = '20230420';

What is the current behavior?
I have sp_blitz installed in master database so that I can run it from any database just by executing "sp_blitz". If I do execute it in a query-window within master database, it correctly shows any TDE certificates that has not been backed up the last 30 days. However, if I am in a query window in any other database and execute sp_blitz, it will not detect the TDE certificates that has not been backed up the last 30 days. The CheckID that I experience this with is 119.

If the current behavior is a bug, please provide the steps to reproduce.
Make sure there are TDE certficates on the server that has not been backed up the last 30 days.
Open a new query window in any database other than master, execute sp_blitz -> It should not report on missing backups for TDE certificates.
Open a new query window in the master database, execute sp_blitz -> It will report missing backups for TDE certificates.

What is the expected behavior?
I would expect sp_blitz to report the missing TDE backups regardless of what database I am in when I execute the script.
I have found that if the query is changed from using sys.certificates to master.sys.certificates, it will give the desired results also when standing in other databases than master.
In the code below I have changed the call to sys.certificates so that it points to the master database: master.sys.certificates, and when I run that query by itself, it will report the missing TDE backups regardless of what database I am in when executing.

`IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 119 )
AND EXISTS ( SELECT *
FROM sys.all_objects o
WHERE o.name = 'dm_database_encryption_keys' )
BEGIN

						IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 119) WITH NOWAIT;

						SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, DatabaseName, URL, Details)
							SELECT 119 AS CheckID,
							1 AS Priority,
							''Backup'' AS FindingsGroup,
							''TDE Certificate Not Backed Up Recently'' AS Finding,
							db_name(dek.database_id) AS DatabaseName,
							''https://www.brentozar.com/go/tde'' AS URL,
							''The certificate '' + c.name + '' is used to encrypt database '' + db_name(dek.database_id) + ''. Last backup date: '' + COALESCE(CAST(c.pvt_key_last_backup_date AS VARCHAR(100)), ''Never'') AS Details
							FROM master.sys.certificates c INNER JOIN sys.dm_database_encryption_keys dek ON c.thumbprint = dek.encryptor_thumbprint
							WHERE pvt_key_last_backup_date IS NULL OR pvt_key_last_backup_date <= DATEADD(dd, -30, GETDATE())  OPTION (RECOMPILE);';
						
						IF @Debug = 2 AND @StringToExecute IS NOT NULL PRINT @StringToExecute;
						IF @Debug = 2 AND @StringToExecute IS NULL PRINT '@StringToExecute has gone NULL, for some reason.';
						
						EXECUTE(@StringToExecute);
					END;`

There is a similar code for CheckId = 202. I have not tested if the problem is the same for that CheckID.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
Microsoft SQL Server 2019 (RTM-CU18) (KB5017593) - 15.0.4261.1 (X64) Sep 12 2022 15:07:06 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

I do not know if the problem is in previous versions of sp_blitz.

[BrentOzar]

Great catch! I've fixed it in the dev branch, and it'll be in the June release with credit to you in the release notes.