Run reports with a database account that cannot write

A reporting tool needs to read orders. The fastest setup is to reuse the application's connection string.

Now a tool that should calculate totals can also update orders. A query editor, an accidental statement, or a new maintenance feature has more authority than reporting requires.

I would give the reporting process its own identity. That makes the intended boundary visible in deployment configuration and enforceable by the database.

Grant the report's actual data

For an existing SQL Server system, start with the tables or views the report needs. A narrow reporting schema or a set of views can be easier to reason about than access to every business table.

Microsoft documents fixed and user-defined database roles. The built-in data-reader role is broad: it is not the same as permission to read one approved report. Check memberships and effective permissions before choosing it for convenience.

A view can also expose more than intended. Review its columns, joins, tenant conditions, and any functions it calls. “The service only runs SELECT” is an application convention, not a complete permission policy.

Use a different credential from migrations and normal business writes. Store and rotate it through the deployment's secret mechanism. Do not put an owner credential behind a read-only checkbox in the interface.

Prove the negative path with the real identity

First, run the expected report query. Then attempt a prohibited write to a disposable fixture table using the same connection settings.

Keep the expected error in a small assertion helper:

fn assert_update_permission_denied(error: &tiberius::error::Error) {
    assert_eq!(error.code(), Some(229));
}

Then use it on the attempted update. This Tiberius excerpt assumes an already connected reporting client:

let error = client
    .execute(
        "UPDATE reporting_permission_probe SET value = @P1 WHERE id = @P2",
        &[&9_i32, &1_i32],
    )
    .await
    .expect_err("reporting account must not update the probe");

assert_update_permission_denied(&error);

SQL Server documents error 229 as an object permission denial. This probe assumes the fixture table exists and the account lacks UPDATE permission on the table. A column-level denial can use another code; build the fixture for the rule you mean to check. A connection failure must fail this assertion rather than count as successful protection.

Then reload the probe through a separate fixture account and confirm its value stayed unchanged. Keep the allowed report query in the same test so a broken connection cannot make the whole permission check look successful. Cover prohibited INSERT, DELETE, and schema changes separately, using their expected errors.

The Rust and SQL Server article covers the client connection and bound values. This permission exercise requires a real SQL Server instance and the configured reporting identity. Compiling a Tiberius call does not prove the server denies it.

Read-only does not mean harmless

A report can still expose private data, read another tenant's records, or consume enough resources to disturb normal traffic. Add query deadlines, bounded result sets, concurrency limits, and the correct customer scope.

Decide whether the report needs current data or can use a replica or prepared dataset. A replica changes freshness and operations; it does not excuse missing authorization. Check what lag means for the report's conclusions.

Stored procedures deserve their own review because execution permissions and ownership rules can provide capabilities beyond a plain table grant. Inventory what the reporting identity can execute, rather than only what it can select directly.

The useful target is small and concrete: the deployed reporting account can run its approved reports, cannot modify the protected business data, and cannot read outside its scope. Keep those checks with the report so future features do not quietly widen the account.

What the live permission fixture establishes

The complete Tiberius fixture now runs against a disposable SQL Server 2022 instance in GitHub CI. Its image is pinned by digest, and the observed engine version is 16.0.4236.2. The client remains Tiberius 0.13.0.

An administrative connection creates the isolated database, the probe table, a private table, and a separate SQL login. The reporting user receives SELECT on the approved probe and explicit table-level denials for UPDATE, INSERT, and DELETE. The test then opens a new connection using that reporting login's own password and database.

The allowed query reads seven. Update, insert, and delete attempts must each return 229. Selecting the ungranted private table must also return 229. Finally the administrative observer reads the protected row and must still see seven. The server response and the stored value therefore agree about the denied write.

The unrelated-transport assertion remains in the smaller article-derived tests. A connection reset cannot satisfy the helper merely because it is an error. The live test adds the engine behavior that compiling the probe could not establish.

The loopback service uses a self-signed certificate and a fixture-only trust setting. That setting is not part of the production connection guidance. Use a verified deployment certificate and the intended server identity outside this isolated service. Disposable passwords are created with the disposable fixture; no production credentials are used.

This is a narrow database permission check, not a complete reporting authorization system. It does not implement tenant filtering in every report, audit every stored procedure, or prove that an expensive SELECT is acceptable. Keep those additional policies beside the report. The useful result here is concrete: the real reporting identity can read its approved data, its forbidden operations receive the intended denial, and the independently observed protected state stays unchanged.