Test-SqlCellKeyHierarchy

This shows, for one database, how its cell-level encryption is set up: which certificates exist, which symmetric keys each one protects, and which users have been granted the right to use them. It changes nothing.

The last part is the one that tends to drift over time — more people able to open a key than anyone intended. This command lays that out so it can be reviewed.

Reads the certificate-to-symmetric-key mapping (sys.certificates / sys.key_encryptions / sys.symmetric_keys) and the principals with permission on those certificates (sys.database_permissions class 25 to sys.database_principals), excluding the built-in ##MS_* certificates. Aggregates per certificate: its symmetric keys (name and algorithm) and its grantees (name and permission). Read-only; never throws; returns a SqlCert.Result.

Recipe 01 — List a database’s cell certificates and who can use them

Test-SqlCellKeyHierarchy -SqlInstance sql01 -Database Sales

Recipe 02 — Surface certificates usable by more than two principals

(Test-SqlCellKeyHierarchy -SqlInstance sql01 -Database Sales).Data.Certificates |
    Where-Object { @($_.Principals).Count -gt 2 }

Candidates for tightening — more grantees than a tightly scoped key usually needs.

Watchpoint — this reports permission, not use. It lists the principals granted permission on each certificate; it does not show who has actually opened a key or decrypted data. Use it to review who could, not who did.

Cell-level encryption stacks keys: a certificate protects a symmetric key, and the symmetric key encrypts the column data. To read a protected column, a user needs permission on the certificate. This command walks that structure for one database and reports it.

It skips the ##MS_* certificates, which are SQL Server’s own built-in ones, and shows only the certificates you created. For each, you see the symmetric keys it protects and the list of principals granted permission on it — the least-privilege view of who can use the encryption.

• Read-only — it reports the cell-encryption configuration without changing it.
• Surfaces the principals permitted on each certificate, the part most prone to privilege drift, for a least-privilege review.
• Excludes SQL Server’s built-in ##MS_* certificates, so the output is the certificates the database owner created.
• Returns structured per-certificate rows (Name, SymmetricKeys of Name/Algorithm, Principals of Name/Permission) — evidence for an audit record.