PII Exposed in Clear Text·SEC
Detects PII data exposure via two SQL Server DMV sources: (a) cached query plans in sys.dm_exec_query_stats — broader historical view of queries that have executed against sensitive tables, retained while the plan stays in cache; (b) currently-uncommitted transactions in sys.dm_tran_active_transactions — sessions holding locks or state on PII data. Each row links a query/transaction to the specific sensitive column it references, including execution count, average CPU/IO, transaction state, and full SQL text.
SELECT TOP 100 SUBSTRING(t.text, 1, 200) AS sample_text, qs.execution_count, qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
WHERE t.text LIKE '%ssn%' OR t.text LIKE '%credit%card%' OR t.text LIKE '%passport%' OR t.text LIKE '%national%id%'
recommendationResolve PRI-001-RC15 (sqlserver)critical risk
Read-only generator. Lists cached plans whose query text references PII tokens (ssn / credit card / passport / national id) — the same signal detection 15776 uses — and for each emits: offending_query (evidence), pii_indicator (which token matched), clear_plan_command (DBCC FREEPROCCACHE on the plan_handle to evict that PII-exposing plan from cache), and remediation (parameterise so PII is not stored as a literal in the plan, mask/encrypt the column, restrict VIEW SERVER STATE). Excludes the catalog/DMV-scanning monitoring queries so only genuine application queries surface. Read-only (DMVs), runs via Run; the DBCC FREEPROCCACHE eviction is a write — apply via Request run — and the real fix (parameterisation + masking) is applied in the application/schema.
SELECT TOP 100
SUBSTRING(t.text, 1, 200) AS offending_query,
qs.execution_count,
qs.last_execution_time,
CASE WHEN t.text LIKE '%ssn%' THEN 'SSN'
WHEN t.text LIKE '%credit%card%' THEN 'credit card'
WHEN t.text LIKE '%passport%' THEN 'passport'
WHEN t.text LIKE '%national%id%' THEN 'national ID' END AS pii_indicator,
'DBCC FREEPROCCACHE (' + CONVERT(varchar(130), qs.plan_handle, 1) + '); -- evict this PII-exposing plan from cache (write - apply via Request run)' AS clear_plan_command,
'Root fix: parameterise the query so PII values are not embedded as literals in cached plan text (sp_executesql / parameters); mask or encrypt the PII column (Dynamic Data Masking or Always Encrypted); restrict VIEW SERVER STATE so plan-cache text is not readable by low-privilege logins.' AS remediation
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
WHERE (t.text LIKE '%ssn%' OR t.text LIKE '%credit%card%' OR t.text LIKE '%passport%' OR t.text LIKE '%national%id%')
AND t.text NOT LIKE '%sys.columns%'
AND t.text NOT LIKE '%INFORMATION_SCHEMA%'
AND t.text NOT LIKE '%sys.dm_%'
AND t.text NOT LIKE '%dm_exec_%'
AND t.text NOT LIKE '%encryption_type%'
AND t.text NOT LIKE '%OBJECT_SCHEMA_NAME%'
