When a rebuild would discard state your package never had

Module 1’s refusals were about the operation: moving a table between partition schemes rewrites every row, so it stops.

This module’s refusals are stranger, because the operation is fine. The column change is ordinary, the data would survive, and SchemaSmith stops anyway — over something that has nothing to do with the change you asked for.

The rebuild guard is one rule, not a list

You met this in Course 8: a column reorder needs a rebuild, and a rebuild was refused because Change Data Capture was on.

CDC is one member of a family. Here is a partitioned table refusing the same reorder, on MySQL and MariaDB:

Table rebuild refused for `vault_m2`.`AuditTrail`: the table is partitioned. A rebuild replaces the
table with a shadow copy, and that state lives outside the schema package -- the copy discards it and
no re-deploy can put it back. Move this table with Before/After migration scripts, or clear the
blocking state first and re-run.

Same sentence, different blocking state. And that is the useful realisation: it isn’t a list of special cases that grows every release. It’s one question asked of whatever the engine happens to be holding — Change Data Capture, system versioning, replication, Change Tracking, inheritance, partitioning, whichever the engine in front of it supports.

The question is always the same. A rebuild builds a new table and copies rows into it. So: is everything attached to the old one written down in your package? Partitioning on the old table isn’t a property of the rows — it’s a property of the object, and the shadow is a different object. CDC’s capture configuration was never in your package at all. Neither can be reconstructed from the model, because neither was ever in it.

Where it does force the change through

A course made only of refusals teaches you that the tool is timid. It isn’t, and this is the beat that proves it.

SQL Server will not alter a column while a schema-bound view references it. It fails with Msg 4922, which names neither the module nor what to do. Here is what SchemaSmith says instead, at exit 2, with the relevant setting off — which is the default:

Column change blocked by SCHEMABINDING: dbo.vw_AuditActors (blocks [dbo].[AuditTrail].[Actor]).
SQL Server will not alter a column while a schema-bound module references it, and SchemaSmith will not
drop a scripted object it cannot put back. Move the listed module(s) into a schema-bound object folder
(QuenchSlot AfterTablesObjects) and set DropSchemaBoundDependents so the deploy can drop them around
the table work and the after-tables object pass recreates them, or remove SCHEMABINDING from them.

The module, the column it blocks, and both remedies. Now turn it on:

{ "DropSchemaBoundDependents": true }

  Drop schema-bound module dbo.vw_AuditActors - blocks a column change; the after-tables object pass recreates it
  Quenched .\widen-allowed\Templates\Main\SchemaBound Views\dbo.vw_AuditActors.sql

Exit 0. The view was dropped, the column widened, the view rebuilt.

Look at where the view came back from: Quenched … SchemaBound Views\dbo.vw_AuditActors.sql — your package, not a copy of what was on the server. That is the whole basis on which the drop was allowed. SchemaSmith did not save the server’s definition and replay it; it dropped an object it could rebuild from the recipe you gave it.

Which is why placement in the package matters. Schema-bound modules belong in a folder on the AfterTablesObjects slot — the default templates ship SchemaBound Views/ and SchemaBound Functions/, and SchemaTongs writes them there on extraction whether or not you use this setting. A module left in the ordinary Views/ folder is recreated before the table work, which is too early to help.

One cost worth knowing before you turn it on: a dropped view loses every GRANT on it. SchemaSmith does not manage permissions on any object, so it has nothing to restore them from. Re-grant in the recreating script.

And where it still refuses

Now make that view WITH ENCRYPTION and run the same widening, setting still on:

Column change blocked by an ENCRYPTED schema-bound module: dbo.vw_AuditActors. Its definition cannot
be read from the server, so nothing can recreate it once dropped -- DropSchemaBoundDependents
deliberately does not extend to it. Remove SCHEMABINDING or the encryption from the listed module(s).

Exit 2. Nothing dropped — the refusal fires before any drop, not partway through one.

OBJECT_DEFINITION returns NULL for an encrypted module. The server will not hand the text back to SchemaSmith, to SchemaTongs, or to you. So the guarantee the entire feature rests on — the after-tables pass will recreate it — cannot be made, and the moment it cannot be made, your opt-in stops applying.

That is worth sitting with. You explicitly turned on a setting that says “drop my schema-bound dependents,” and SchemaSmith declined anyway, in the one case where honouring it would be unrecoverable. An opt-in that quietly became destructive in an edge case would be worse than no opt-in at all — because you’d have stopped reading the deploy log.

Check yourself: Your team turns on DropSchemaBoundDependents to unblock a column change. The deploy succeeds, but two days later a reporting service starts failing with permission errors on a view that still exists and returns correct data. What happened?

The view was dropped and recreated during the deploy — and a dropped object loses every GRANT on it. SchemaSmith recreated the view from your package script, exactly as designed, but it does not manage permissions on any object, so there was nothing for it to restore the grants from. The view is correct; the reporting service’s access to it is gone.

The fix is to re-grant in the recreating script itself, so the permission travels with the definition that depends on it, or from whatever process already manages your permissions. And the lesson generalises past this setting: when a tool recreates an object from your package, anything about that object that is not in your package does not come back. That is the same sentence as the rebuild guards — it is only survivable here because a GRANT is recoverable, where a CDC configuration is not.

Three postures toward one table, in one module. SchemaSmith converged the column when nothing stood in the way. It refused when a rebuild would have discarded state the package never described. And in between it acted — dropping and rebuilding a dependency, because your package held the recipe.

That middle case is what makes the other two coherent. The tool is not refusing out of caution. It is answering a specific, checkable question every time: can I reconstruct what I am about to destroy? Where the answer is yes, it does the work without being asked twice. Where the answer is no, it stops — and tells you which of the two you’re looking at.

Next: Module 3 — the refusal you did not ask for, where the engine would have done exactly what your package said, and SchemaSmith declines anyway.

Until then, may everything you drop be something you can build again.

— Forge

Mark this lesson complete