The most uncomfortable failure mode for a database is the one that never writes a single byte. A practitioner who connected a coding agent to a fifteen-year-old, 312-table SQL Server monolith reported the sequence this week: the agent ran on a strict db_datareader login, every query passed a regex that checks the statement starts with SELECT, and within four minutes three things went wrong at once. The agent pulled INFORMATION_SCHEMA and ingested 433,876 bytes (108,469 tokens) of data definition. A parameter the driver bound as NVARCHAR(4000) against an indexed VARCHAR(50) column forced the optimizer to re-write the predicate across the index, pinning cores 0 through 7 at 100 percent. An unindexed exploratory join collected 5,000 shared page locks, escalated to a table-wide TABLOCK S, and locked forty technicians out of recording cryo-vial movements. Nothing was mutated. Nobody could work.
I am relaying those numbers from that report, not re-running them. They are single-sourced, and the point is not the precise counts but the shape of the failure: all of it happened on a login that could not write. The read-only role was real. It simply was not the wall.
The first-order advice is already everywhere: split the schema dump into lazy list_tables() and describe_table() calls, enforce SELECT TOP 100, revoke write privileges. That stops the easy stuff. What takes down a mature production estate is a second layer of mechanics that the read-only role and a SELECT-regex both miss. There are four, and all of them sit below the permission boundary.
The four channels a read-only role does not close
The first is husk tables. A fifteen-year-old schema keeps dozens of zero-row relics next to the live ones, each with a plausible name, a clean primary key, and exactly no data. An agent handed a flat list of 312 cryptic names will pick the one that reads most clearly, write a syntactically flawless join, get zero rows, and burn turns diagnosing an outage that was never there. The report counted 45 such husks beside 267 populated tables, and measured the lazy-discovery loop replaying its own tool output into the context window, accumulating 346,060 bytes of transcript drag over twelve round-trips.
The second is the driver type-precedence trap. Most generic wrappers bind a string parameter as NVARCHAR(4000) when the column type is not given. Under SQL Server type-precedence rules, VARCHAR ranks below NVARCHAR, so instead of narrowing one parameter once, the engine widens every indexed row at runtime and degrades a sub-millisecond index seek into a multi-million-row scan. Forcing everything to VARCHAR instead quietly corrupts the Unicode columns that hold scientific units.
The third is shared-lock escalation. Without row-versioned snapshot isolation, a poorly selective SELECT can collect enough shared locks to escalate to a table-wide read lock, stalling live INSERT and UPDATE commits. The read-only role granted the SELECT. It said nothing about how many locks that SELECT would take.
The fourth is side-channel constructs that pass a SELECT-regex. A statement can begin with SELECT and still do damage: an ad-hoc OPENROWSET coerces the server into an outbound network call, a recursive CTE with MAXRECURSION 0 pins the CPU, and a broad VIEW DEFINITION lets the agent read OBJECT_DEFINITION to scrape credentials that were hardcoded in 2016 and never rotated.
None of these is caught by "the login cannot write." They are caught by what the statement is doing, not by what the login is allowed to do.
Layer one: a static guard that rejects the side channels
The cheapest thing you can put in front of the agent is a check that runs on the statement text before it ever reaches the socket. It answers two questions: does the statement have a row cap, and does it contain a construct that a read-only role does not stop? It returns an exit code, so it plugs into a CI step or a pre-tool hook with fail-closed semantics.
#!/usr/bin/env python3 """static_db_guard.py: reject the side-channel SQL a SELECT-regex misses. Checks, in order: 1. Is it a single statement? (no top-level ';') 2. Does it start with SELECT or WITH? 3. Does it contain a side-channel construct read-only does not stop? 4. Does it carry a row cap (TOP n / LIMIT n / FETCH FIRST n)? Exit codes: 0 = clean single SELECT with a row cap 1 = blocked (side channel, or missing row cap) 2 = could not classify (empty, multi-statement, non-SELECT) Usage: static_db_guard.py <statement-file> """ import re import sys BLOCKED = [ (r"\bOPENROWSET\b", "EXTERNAL_ROWSET_SSRF"), (r"\bOPENDATASOURCE\b", "EXTERNAL_ROWSET_SSRF"), (r"\bOPENQUERY\b", "EXTERNAL_ROWSET_SSRF"), (r"\bOBJECT_DEFINITION\b", "PROC_SECRET_SCRAPE"), (r"\bsys\.sql_modules\b", "PROC_SECRET_SCRAPE"), (r"\bsp_helptext\b", "PROC_SECRET_SCRAPE"), (r"\bWAITFOR\s+(?:DELAY|TIME)\b","POOL_EXHAUSTION"), (r"\bINTO\s+[#@A-Za-z_\[]", "SELECT_INTO_MUTATION"), (r"\bNOLOCK\b", "DIRTY_READ"), (r"\b(?:TABLOCKX|UPDLOCK|XLOCK)\b", "WRITE_LOCK_HINT"), ] def classify(sql): s = sql.strip().rstrip(";").strip() if not s: return 2, "EMPTY_STATEMENT" body = re.sub(r"'(?:''|[^'])*'", "''", s) # blank out string literals body = re.sub(r"--[^\n]*", " ", body) # strip line comments if ";" in body: return 2, "MULTI_STATEMENT" if not re.match(r"^(SELECT|WITH)\b", s, re.I): return 2, "NOT_SELECT_ROOT" for pattern, code in BLOCKED: if re.search(pattern, s, re.I): return 1, code if not re.search(r"\bTOP\s*\(|\bLIMIT\s+\d|\bFETCH\s+FIRST\s+\d", s, re.I): return 1, "MISSING_ROW_CAP" return 0, "CLEAN" if __name__ == "__main__": if len(sys.argv) != 2: print("usage: static_db_guard.py <statement-file>") sys.exit(2) with open(sys.argv[1], encoding="utf-8") as handle: sql = handle.read() code, why = classify(sql) label = "PASS" if code == 0 else "BLOCK" if code == 1 else "UNCLEAR" print(f"{label} {why}") sys.exit(code) I ran it against four statements to show the three states:
$ python3 static_db_guard.py clean.sql # SELECT TOP (50) id, name FROM orders PASS CLEAN # exit 0 $ python3 static_db_guard.py no_cap.sql # SELECT * FROM information_schema.tables BLOCK MISSING_ROW_CAP # exit 1 $ python3 static_db_guard.py rowset.sql # SELECT TOP (5) * FROM OPENROWSET(...) BLOCK EXTERNAL_ROWSET_SSRF # exit 1 $ python3 static_db_guard.py two.sql # SELECT 1; DROP TABLE x UNCLEAR MULTI_STATEMENT # exit 2 The NOLOCK rule is the one worth pausing on. It is not a write. But a dirty read lets the agent ingest uncommitted rows that later roll back, so it reasons from phantom data and then repeats those numbers downstream. The report bans NOLOCK for exactly that reason, and the guard inherits the ban.
Layer two: measure how husk-heavy your schema is before you connect
The static guard stops the statement-level traps. It cannot tell you whether your schema is the kind that will burn the agent's turns on empty decoys. That you have to measure directly. On SQL Server:
-- husk ratio: populated tables vs zero-row relics in the schema SELECT COUNT(*) AS total_tables, SUM(CASE WHEN rows > 0 THEN 1 ELSE 0 END) AS populated, SUM(CASE WHEN rows = 0 THEN 1 ELSE 0 END) AS zero_row_husks FROM sys.tables t JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0, 1); On PostgreSQL the same idea is pg_stat_user_tables filtered on n_live_tup = 0. I have written these as templates and have not run them against a production database, so treat the thresholds as yours to calibrate in staging. The report I am drawing on measured a ratio of 45 zero-row tables beside 267 populated ones; your number will be different, and it is the number that decides whether the agent needs a curated subgraph before it is allowed to describe_table freely.
Wiring it into the tool path
The guard is only as useful as the thing that calls it. Two placements work in practice. The first is a CI step that runs the guard over every statement the agent proposes before the query or migration is executed; a non-zero exit blocks the job, and because the check is fail-closed, a statement it cannot classify blocks the same way as one it does not like. The second is a pre-tool hook in the agent harness: the agent writes its proposed SQL to a file, the hook runs the guard, and a non-zero exit stops the tool call before it reaches the database at all.
Both placements rely on one property: the guard reads the statement, not the login. The role behind it is the only thing the database enforces, and the guard is the only thing that enforces the four channels above. Get the role wrong and the wall is missing. Get the guard wrong and the four channels come back. They are not substitutes for each other, which is why the probe, the guard, and the role are three separate checks rather than one.
Where the two layers meet
The guard is a request. The role is the wall. A static guard in front of the agent, a least-privilege read-only role behind it, and a schema probe before the first connection are three separate decisions, and only the role is the one the database actually enforces. The guard exists because the agent is not the thing you are trusting. The role exists because the agent is. And the probe exists because you are not going to learn how many husks you carry the first time the agent joins one and gets nothing back.
Run the probe, look at your own husk ratio, then decide how much of the schema the agent may see. Give context generously. Give access stingily.
Sources
- tomzafred, dev.to, 2026-11-15. First-person report of a coding agent on a 312-table SQL Server schema, including the 433,876-byte
INFORMATION_SCHEMApull, theNVARCHAR(4000)type-precedence trap, the 5,000-lock escalation toTABLOCK S, and the 45-husk/267-populated count. All figures above are from this report and were not independently re-run. - honeykiran, dev.to, 2026-11. Read-only database access for agents, including the finding that a stray
COMMITended the read-only transaction a reference wrapper had opened. Cited for the "a wrapper's promise is a request, a rule on the role is a wall" framing.
This post was written with AI assistance. The author is responsible for its content.