The security layer, declared

🧪

Hands-on lab

Run this module yourself — the `before/` source, the three-engine sandbox, and the worked solution to diff against.

Demos/Learn/course12-module-11 →

Ask who built the row-level security policy and you’ll get a name, a year, and a shrug. Same for the signed reporting procedure, the encrypted SSN column, and the trigger someone disabled “temporarily” in 2019. They’re the objects that guard the database, and they’re the ones a deploy is most likely to break without anyone noticing: a policy dropped and never put back, a signature stripped by an ALTER, a disabled trigger quietly re-enabled.

From 2.8, SchemaTongs extracts all of them. Here’s what each one becomes, and what a deploy does with it.

What comes out of the database

The lab builds the layer by hand in declared_m11, then extracts it:

  Tables:     3 extracted, 0 errors
  Procedures: 1 extracted
  Triggers:   3 extracted
  Security policies: 1 extracted

A row-level security policy goes to SecurityPolicies/, its predicate function to SchemaBound Functions/. The policy script reads the live policy, compares it with the extracted one, and re-creates it only when they differ, inside a transaction.

Always Encrypted keys go to Prerequisites/AlwaysEncryptedKeys.sql, which runs before the tables and creates each key only where it’s missing. It’s metadata, never key material: KEY_PATH names a certificate outside the database. The encrypted column itself is a table column with EncryptionType, EncryptionKey and EncryptionAlgorithm.

A trigger’s state and order aren’t part of its definition, so each trigger script ends with the batch that restores them:

DISABLE TRIGGER [dbo].[trg_Orders_Legacy] ON [dbo].[Orders]
EXEC sp_settriggerorder @triggername = N'[dbo].[trg_Orders_Stamp]', @order = N'First', @stmttype = N'INSERT'

Without that tail, every deploy of the trigger would leave the legacy one enabled and the first one unordered.

A signed procedure comes out without its signature. A signature needs the certificate’s private key, and no tool can read that out of a database.

What a deploy does with it

Deploy the package back over the same database, and the procedure script runs, which strips the signature. SchemaQuench saw the signature before the script ran, so it signs the procedure again with the same certificate:

Signing [dbo].[usp_RegionTotals] again

The summary records it as resigned. That works because the certificate’s private key is protected by the database master key, which the server opens without a password. A key protected by a password can’t be used that way, and then the deploy warns by name that the module needs signing by hand.

Change the procedure later and the same thing happens: the ALTER strips the signature, the deploy puts it back. A procedure the deploy doesn’t change isn’t altered at all, so its signature is never touched.

One thing to plan for. A schema-bound predicate function can’t be altered while a policy uses it, so when the function’s script runs (on the first deploy, and whenever you change the function) the deploy takes the policy off first and the policy script puts it back moments later. Treat a predicate function change like a change to the policy itself.

Into a database that has never seen it

Deploy the same package to a fresh database and the layer arrives whole: the policy, the keys ahead of the encrypted column that needs them, the disabled trigger still disabled. The signed procedure arrives unsigned, and nothing warns, because there was never a signature there to lose.

That’s the one piece of the security layer that can’t travel in a package. Set up signing once per environment: create or restore the certificate, sign with ADD SIGNATURE in a migration script. From then on, every deploy that alters the procedure signs it again.

Check yourself: A procedure signed with a certificate is changed in the package and deployed. What happens to the signature, and what would happen in a database where that certificate's private key is protected by a password?

The ALTER strips it, and SchemaQuench signs the procedure again with the same certificate, recording resigned in the summary. It can only do that with a key whose private key is protected by the database master key, which the server opens without a password. With a password-protected key it can’t sign, so the change still applies and the deploy warns by name that the module needs signing by hand.

---

The lock, the strongbox and the stamp on the door are part of the forge too. You don’t leave them out of the inventory because they’re awkward to describe. Write down the policy, the keys, the trigger that stays off and the one that goes first. The deploy keeps them where you left them, and stamps the seal again when it has to open something. Only the signet ring stays in your pocket.

Guarding a database with something you’re not sure SchemaSmith can extract? Email me at forgebarrett@schemasmith.com — I read every one.

Until then, may every guard you set stay standing through every deploy.

— Forge

Mark this lesson complete