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,          // an admin already installed SchemaSmith's objects
  "UpdateTables": false,            // skip the structural table-quench phase entirely
  "DropTablesRemovedFromProduct": false,  // never drop a table by absence
  "TrackRunOnceMigrations": true    // run the fix once per tenant, and record it
}

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 means the fix runs once per tenant: SchemaQuench notes it in each database and skips it on every later deploy. We’ll still make the script idempotent — a record can be cleared and a fix replayed by hand, and money shouldn’t move twice either way.

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.

There’s one more grant, and it waits on an admin’s one-time job. An admin kindles each tenant — deploys a package with no scripts in it, which installs SchemaSmith’s own objects — and among them is the run-once record, the table where a deploy notes “this fix ran here.” The datafix account gets that one table and nothing else of SchemaSmith’s:

GRANT SELECT, INSERT, DELETE ON SchemaSmith.CompletedMigrationScripts TO datafix_user;

Leave it out and the deploy refuses before it runs anything — exit 2, naming the table and the grant — rather than quietly running your fix again next time.

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-once tracking means it runs once per tenant anyway; the guards are what keep a replay — by hand, or after someone clears the record — from moving money twice.

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 the log says Skipping (previously quenched) …01_backup_and_fix_prices.sql: the record did its job, and the backup still holds 10. 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