Fix production data with limited privileges

A pricing bug just shipped. For one stretch of May, every order line got a discount applied twice — UnitPrice × 0.81 where it should’ve been × 0.90. It’s live, it’s money, and it’s sitting in every tenant database you run. You need to fix it today. But you can’t hand that fix a god-mode account, and if you get the numbers wrong you have to be able to put every row back exactly as it was.

Hey folks. I’m Forge Barrett, master of the Content Forge here at SchemaSmith.

This is a datafix — a data-only change, shipped through the same tool and the same package you deploy schema with, but under guardrails that make it safe to run against production. No structural work, a scoped account, a backup of every row you touch, and one tenant fixed first before you trust it on the rest.

A datafix does no structural work

A full quench reconciles structure — it’ll add a column, rebuild an index, drop a table that left the package. A datafix wants none of that. It runs your migration script and nothing else. Four flags in the deploy settings say so:

{
  "KindleTheForge": false,          // don't create or rebuild databases
  "UpdateTables": false,            // skip the structural table-quench phase entirely
  "DropTablesRemovedFromProduct": false,  // never drop a table by absence
  "TrackRunOnceMigrations": false   // every migration script runs on every deploy
}

UpdateTables: false is the load-bearing one — it means SchemaSmith never enters the phase where it would touch a table’s shape. The hammer stays holstered. That’s the first guardrail: the profile simply won’t do structural work, no matter what’s in the package.

That last flag has a consequence worth pausing on. With run-once tracking off, your script runs every single deploy — so it has to be idempotent. Run it once, run it ten times, the result is identical. We’ll build that in.

The account that can’t overreach

The second guardrail is the credential. The lab’s course6-setup created a datafix_user that can read and write your data — and owns a private datafix schema for its backup tables — but has no power over the structure of your product tables. On SQL Server that’s the whole grant:

CREATE SCHEMA datafix AUTHORIZATION datafix_user;   -- the account owns this schema
GRANT SELECT, INSERT, UPDATE ON SCHEMA::dbo TO datafix_user;   -- read/write data
GRANT CREATE TABLE TO datafix_user;                 -- for the backup, which lands in 'datafix'
GRANT EXECUTE ON SCHEMA::dbo TO datafix_user;

Notice what’s not there: no ALTER ON SCHEMA::dbo. That’s the grant a DBA reaches for when you ask for “rights to create a table” — and it would also let this account drop and rewrite every table in dbo. You don’t need it. Because the account owns the datafix schema, it can create its backup table there with plain CREATE TABLE and zero authority over dbo. You asked for a hammer; you got a hammer, not the keys to the whole smithy — and you can prove it.

Back up first, then fix — idempotently

Here’s the migration. It creates the backup table in the owned datafix schema, copies in every affected row it hasn’t already saved, then corrects the prices it hasn’t already corrected:

IF OBJECT_ID('datafix.OrderItem_PriceFix_Backup') IS NULL
    CREATE TABLE datafix.OrderItem_PriceFix_Backup (
        OrderItemId INT NOT NULL PRIMARY KEY,
        OldUnitPrice DECIMAL(10,2) NOT NULL,
        BackedUpAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME());

INSERT INTO datafix.OrderItem_PriceFix_Backup (OrderItemId, OldUnitPrice)
SELECT oi.OrderItemId, oi.UnitPrice
FROM dbo.OrderItem oi JOIN dbo.SalesOrder so ON so.OrderId = oi.OrderId
WHERE so.OrderDate >= '2026-05-01' AND so.OrderDate < '2026-06-01'
  AND NOT EXISTS (SELECT 1 FROM datafix.OrderItem_PriceFix_Backup b WHERE b.OrderItemId = oi.OrderItemId);

UPDATE oi SET oi.UnitPrice = ROUND(p.UnitPrice * 0.90, 2)
FROM dbo.OrderItem oi
JOIN dbo.Product p ON p.ProductId = oi.ProductId
JOIN datafix.OrderItem_PriceFix_Backup b ON b.OrderItemId = oi.OrderItemId
WHERE oi.UnitPrice <> ROUND(p.UnitPrice * 0.90, 2);

Two guards make it idempotent. The NOT EXISTS means the backup captures each original price exactly once — a second run adds nothing. The WHERE oi.UnitPrice <> ROUND(...) means the update skips rows already correct — a second run changes nothing. Run it as many times as the datafix profile makes it run; the first pass does the work, every pass after is a clean no-op.

Canary: fix one tenant, watch it, then widen

The deploy settings target a single database — your canary:

"Target": { "User": "datafix_user", "Password": "DataFix!Demo123", "Databases": ["shop_tenant_a"] }

cd sqlserver
schemaquench --ConfigFile:quench.settings.json

[Target] Resolved 1 work unit(s) after filtering 3 discovered unit(s) for template 'Main'.
[localhost,11433].[shop_tenant_a]     Quenching .\Package\...\After Scripts\01_backup_and_fix_prices.sql
[localhost,11433].[shop_tenant_a] Successfully Quenched

One of three databases touched. Connected as datafix_user, no table-quench phase, just the migration. (That’s the SQL Server run — the PostgreSQL and MySQL labs follow the identical steps in their own engine folders.) Now prove it landed:

SELECT COUNT(*) FROM datafix.OrderItem_PriceFix_Backup;   -- 10  (originals saved)
-- May rows still mispriced in tenant_a:
SELECT COUNT(*) FROM dbo.OrderItem oi
  JOIN dbo.SalesOrder so ON so.OrderId = oi.OrderId
  JOIN dbo.Product p ON p.ProductId = oi.ProductId
 WHERE so.OrderDate >= '2026-05-01' AND so.OrderDate < '2026-06-01'
   AND oi.UnitPrice <> ROUND(p.UnitPrice * 0.90, 2);       -- 0  (all fixed)

Ten rows backed up, zero still wrong — and shop_tenant_b and shop_tenant_c untouched, no backup table, defect intact. The canary changed exactly one tenant. Re-run the same command and nothing moves: backup stays at 10, zero rows update. That’s the idempotency you built, proven. Happy with the canary? Widen Target.Databases to ["shop_tenant_b","shop_tenant_c"] and deploy again. The fleet’s done.

Prove the boundary

Two guardrails held. The profile never entered a table-quench phase — and the account couldn’t have done structural damage even if it had. Try it yourself, as datafix_user:

DROP TABLE dbo.OrderItem;   -- Msg 3701: ... you do not have permission.

Denied. The account can back up and fix data all day; it cannot drop or alter the tables it’s protecting. The boundary isn’t a flag you hoped you set right — it’s a power the credential physically doesn’t have.

Per-engine notes

The shape is identical on all four engines: data-only profile, a scoped account that owns a datafix schema for its backup, canary then widen. The boundary is reached a little differently per engine — but it’s reached everywhere.

	SQL Server	PostgreSQL	MySQL
Product schema	`dbo`	`public`	the tenant database
Backup lives in	`datafix` schema (owned)	`datafix` schema (owned)	the tenant database
Why it can’t drop	no `ALTER ON SCHEMA::dbo`	not the owner of `public` tables; no `CREATE` on `public` either	no `DROP` privilege granted
Drop attempt returns	`you do not have permission`	`must be owner of table …`	`DROP command denied`

MySQL has no schema-inside-a-database layer, so its backup table sits in the tenant database and the boundary comes from simply never granting DROP. The two schema-capable engines give the account its own datafix schema — same principle, native to each.

Check yourself: The datafix migration creates a backup table, so the account needs CREATE TABLE. Why doesn't that also let it drop your product tables?

Because the backup table is created in a datafix schema the account owns, not in the product schema (dbo / public). Creating a table in a schema you own needs only CREATE TABLE — no rights over the product tables at all. The grant a DBA might otherwise hand you, ALTER ON SCHEMA::dbo, would let you place the backup in dbo and drop everything else there. Owning a dedicated schema sidesteps that: CREATE TABLE in your own schema, zero structural power over the product’s.

A good smith keeps a slack tub close, but never lets it touch the wrong steel. That’s a datafix — you reshape the data that’s wrong, you keep the originals cooling in a tub that’s yours alone, and the tables you’re not fixing never feel the heat. Least power that still does the job, one tenant proven before the fleet.

Got a datafix you’re not sure how to scope, or an account you suspect is wider than the job needs? Email me at forgebarrett@schemasmith.com — I read every one.

More’s coming from the forge — pre-flight checks, CI gates, and packaging a patch down to the objects that changed.

Until then, may every fix undo as clean as it applied, your originals keep safe in a tub that’s yours, and the rows you never touched stay exactly as they were.

— Forge

Mark this lesson complete