Give SQL Server Applications Procedure Access, Not db_owner

An application that only needs an invoice summary does not need the ability to edit every table or change the database schema. Granting db_owner may make deployment errors disappear, but it also hides which operations the application actually depends on. A small permission surface is easier to review and easier to keep stable as the database grows.

Stored procedures can provide that surface when their bodies expose the intended operations and their ownership relationships are understood. The useful question is not "can this login connect?" but "which reads and writes can this application perform, through which supported entry points?"

Grant the operation through a role

This example creates a practice table with email addresses and a procedure returning only a count and total. Use a disposable database. GO is a client batch separator, so execute the batches separately if your driver does not understand it.

-- Run in a disposable practice database as its administrator.
CREATE TABLE dbo.PermissionInvoiceDemo (
    InvoiceId int PRIMARY KEY,
    CustomerEmail nvarchar(200) NOT NULL,
    Total decimal(12,2) NOT NULL
);
INSERT dbo.PermissionInvoiceDemo VALUES
(1, N'example1@example.invalid', 40.00),
(2, N'example2@example.invalid', 60.00);
GO
CREATE PROCEDURE dbo.GetInvoiceSummaryDemo
AS
BEGIN
    SET NOCOUNT ON;
    SELECT COUNT_BIG(*) AS InvoiceCount, SUM(Total) AS InvoiceTotal
    FROM dbo.PermissionInvoiceDemo;
END;
GO
CREATE ROLE InvoiceSummaryReaderDemo AUTHORIZATION dbo;
CREATE USER InvoiceReportUserDemo WITHOUT LOGIN;
ALTER ROLE InvoiceSummaryReaderDemo ADD MEMBER InvoiceReportUserDemo;
GRANT EXECUTE ON OBJECT::dbo.GetInvoiceSummaryDemo
TO InvoiceSummaryReaderDemo;

The database user is deliberately created WITHOUT LOGIN for a local permission test. It is not an application connection account. In deployment, create or map the actual application user using the authentication model your environment already uses and add it to the narrow role.

The role receives EXECUTE on one procedure. It receives no general SELECT permission on the table. Both objects use the same owner through dbo, and the procedure contains static SQL. Under an unbroken ownership chain, SQL Server can authorize the procedure call without separately requiring the caller's table permission.

The expected summary is two invoices and a total of 100.00. The email addresses are not projected. This example demonstrates an object access boundary, not a universal privacy guarantee: even aggregates need review when small groups or sensitive categories could reveal information.

Know where the boundary stops

A procedure is not automatically safe merely because its caller lacks table access. If it accepts arbitrary object names, runs unvalidated dynamic SQL, or returns every tenant's rows, the caller may still obtain excessive capability through the procedure itself. Review the body as part of the permission grant.

Dynamic SQL does not benefit from the same static ownership-chain assumption. Using sp_executesql with parameters addresses value handling and injection risk, but does not automatically grant the permissions required by the generated statement. Do not fix that surprise by making the application db_owner.

Where dynamic or cross-boundary access is necessary, consider a carefully scoped module-signing design or a deliberately chosen execution context. Certificate signing can add narrowly granted permissions while the module runs, but deployment must preserve the signing process. Altering a signed module requires re-signing it. Keep certificate management and the exact granted capability reviewable.

Avoid enabling TRUSTWORTHY or broad cross-database ownership chaining as a routine permission repair. Those settings change a wider trust boundary than a single procedure call. Choose an explicit design for the particular cross-database operation.

Test as the application identity

A successful test under sysadmin proves little about the application's permissions. The following impersonation test executes the summary as the limited database user and restores the original context even if execution fails.

EXECUTE AS USER = N'InvoiceReportUserDemo';
BEGIN TRY
    EXEC dbo.GetInvoiceSummaryDemo;
END TRY
BEGIN CATCH
    REVERT;
    THROW;
END CATCH;
REVERT;

Also verify that direct access to CustomerEmail is denied under that identity. Test both allowed and forbidden operations. Catalog grants alone are incomplete because role memberships, explicit grants, denies, ownership, and higher-level privileges can change effective access.

Separate the deployment identity from the runtime identity. Deployment may need to create or alter procedures; normal requests should not inherit those privileges. After a release adds a new operation, grant that operation deliberately rather than expanding the runtime role to all present and future objects.

Review schema-level EXECUTE grants carefully. They can be useful for a deliberately managed API schema, but automatically extend to future procedures in that schema. That is a governance choice, not simply shorter syntax.

Keep a permission test alongside meaningful release checks: the application can execute its supported operations, cannot read protected columns directly, and cannot alter objects. The resulting design gives developers a clear database contract and makes an accidental privilege increase visible before it becomes the normal configuration.

Technical references: Microsoft Learn: Database Engine permissions · Microsoft Learn: GRANT object permissions · Microsoft Learn: Sign a procedure with a certificate.