Testing what the app login is refused
In the overview of RecipeBox I said the web app's database login can run four stored procedures and read nothing. That is a claim about a control. I have spent enough years around STIG checks to know what an unverified control is worth: it is a hope with a ticket number.
Why working proves nothing
The obvious test is to load the site and see if it works. It does, and that tells you almost nothing. A login with far too much access passes every functional test there is, because everything works. Over-permission is invisible to a test that only asks "can it do its job".
The only way to see it is to attempt the things that ought to fail and assert that they did. So the permission script has two halves. The four procedures the app needs must succeed. Everything else must be refused: every table, the views over them, every other procedure, any write at all, and any other database on the instance.
What counts as a refusal
An empty result is not a refusal. If a future migration granted SELECT on a table, a test that
only checked for "no rows" could keep passing while the boundary was gone. So every negative case
asserts on the SQL error number, and a query that unexpectedly succeeds is a failure.
try { $reader = $cmd.ExecuteReader(); Fail 'SUCCEEDED, must be refused' }
catch [SqlException] {
if ($_.Exception.Number -in 229, 230, 262, 916) { Pass }
else { Fail 'refused, but not by a permission error' }
}
Those numbers are object permission denied, column denied, statement denied, and a database the
login cannot enter. The views get their own section because a view is a table by another name.
The script also lists what the login actually holds from the permission catalog and checks that
there are exactly four EXECUTE grants, no more.
Where the grants live
The grants are not in a one-off setup script. The rebuild script drops every procedure, and an object-level grant dies with its object. The user and the role survive, so after a rebuild the login still authenticates and can do nothing, and fails on every page.
So the grants live in a migration that runs on every rebuild, given to a role rather than to the
user directly, as four named grants and not EXECUTE on the whole schema. The schema form is
less typing and decays: it would quietly cover every procedure added later, including ones
written by someone who forgot this file exists. I run the test on either side of a rebuild,
because a design that passed once and failed after the next teardown is the one I most wanted to
catch.
This is also why the procedures use CREATE OR ALTER. My notes record that it preserves object
permissions, while DROP followed by CREATE does not. Re-applying a procedure never leaves the
login without EXECUTE, not even for an instant.
The test was wrong, twice
The first run reported 16 passed and 1 failed. The failure was my test, not the login. I had probed the system view of server logins, watched it succeed, and called it a breach. But every login can reach the built-in system databases, and that view only shows a principal its own row. A comparable account with no server roles saw the same thing.
My replacement was wrong in the same family. I counted databases visible to the login and
expected one. SQL Server grants VIEW ANY DATABASE to public by default, so every login can
read every database name. The comparable account returned the identical count. Seeing a name is
not reaching the data, so the check now asks whether the login has access, and tries a named
other database directly and expects a refusal.
The honest part is that I had the evidence on screen two commits earlier and asserted something it contradicted. The rule I wrote into the script and the docs: before asserting a permission is absent, run the same query as a comparable account. Anything that account can also do is the instance's baseline, not a grant to the login under test. And a test that cries wolf gets ignored, which makes a false positive worse than no test.
The real bug it shook loose
Checking the first false alarm turned up a genuine defect, with the same root cause. The migration decided whether to create the user by looking in the server principals view. It runs as an account that cannot see that row, so the check was always false and the whole block was dead code. It hid well: on my machine the user and role survived rebuilds, so everything kept working. It would have surfaced on a fresh clone or a restore onto another instance, where the user would never be created and the site could not connect at all.
The fix was to ask a function that is not subject to metadata filtering. I proved it against the broken scenario, dropping the database user and keeping the login, and the migration recreated and relinked it.
The app side
The app does its part by keeping two failures apart. An unreachable database gets a friendly 503 page. A permission error stays a loud 500, on purpose. A missing grant should look like a missing grant, not like the server being off overnight.