Giving an Application Account Only What It Needs

A least privilege SQL Server design gives an application enough access to do its job and no unrelated authority. Start with the operations the application performs, then build a role around that contract.

Separate Runtime From Deployment

An application may need to read orders and execute a payment procedure. That does not mean its runtime account should create tables or change permissions. Deployment and daily operation have different responsibilities.

Using db_owner because installation was easier leaves broad authority available during every ordinary request. A defect or compromised credential then has more room to act. Keep elevated deployment access separate and deliberately controlled.

Write down the real runtime operations before granting anything. Include scheduled work, reporting paths, and error-handling operations. Missing one background task often explains why a narrow role appears to work until the first overnight run.

Inventory Existing Access Before Replacing It

Review role memberships and explicit grants for the current account. Also check inherited access through other roles and Windows groups. Removing one visible grant does not necessarily remove the effective permission.

SELECT r.name AS role_name, m.name AS member_name
FROM sys.database_role_members AS rm
JOIN sys.database_principals AS r
  ON r.principal_id = rm.role_principal_id
JOIN sys.database_principals AS m
  ON m.principal_id = rm.member_principal_id;
SELECT USER_NAME(grantee_principal_id) AS grantee,
       state_desc, permission_name, class_desc, major_id
FROM sys.database_permissions;

Run the inventory with sufficient metadata visibility and retain the findings for the change review. Do not strip production permissions by trial and error. Build and test the intended role in an approved environment first.

Build a Narrow Lab Role

The following example creates disposable objects in a dedicated test database. Use a fresh lab database where these names do not exist. It demonstrates a user without a login so no new server credential is required.

CREATE TABLE dbo.AccessLabOrders
(
    OrderId int PRIMARY KEY,
    Amount decimal(12,2) NOT NULL
);
INSERT dbo.AccessLabOrders VALUES (1, 25.00);
CREATE ROLE AccessLabReader AUTHORIZATION dbo;
CREATE USER AccessLabUser WITHOUT LOGIN;
ALTER ROLE AccessLabReader ADD MEMBER AccessLabUser;
GRANT SELECT ON OBJECT::dbo.AccessLabOrders TO AccessLabReader;

The role can read this table because that operation was explicitly granted. It has no reason to alter the table or delete orders. A real application might instead receive EXECUTE on a controlled procedure.

Choose object-level grants when the interface is small and specific. Schema-level grants can simplify larger interfaces, but include future applicable objects in that schema. Review that broader scope before using it.

Test the Positive and Negative Cases

Use the lab user to check the intended permission boundary. The impersonation requires appropriate authority in the test environment. Always return to the original context after the test.

EXECUTE AS USER = N'AccessLabUser';
BEGIN TRY
    SELECT OrderId, Amount FROM dbo.AccessLabOrders;
    SELECT HAS_PERMS_BY_NAME(N'dbo.AccessLabOrders', 'OBJECT', 'SELECT')
             AS can_select,
           HAS_PERMS_BY_NAME(N'dbo.AccessLabOrders', 'OBJECT', 'DELETE')
             AS can_delete;
    REVERT;
END TRY
BEGIN CATCH
    REVERT;
    THROW;
END CATCH;

This verifies a simple database-level example. It does not reproduce network authentication, connection pooling, or external resources. Test those through the actual application identity before handing over the account.

Include negative tests for operations that should remain unavailable. A successful login alone tells you very little about the boundary. Keep the test cases with the role definition so future grants can be reviewed against them.

Account for Procedure Execution Paths

Static SQL inside a procedure can use ownership chaining when the relevant owners match. That can let callers execute the interface without direct access to its underlying tables. Dynamic SQL has different permission behavior.

When a controlled module needs additional authority, investigate module signing or an appropriate execution-context design. Do not jump directly to making the application an owner. Each approach has a trust boundary that must be understood.

SELECT USER_NAME() AS current_database_user,
       ORIGINAL_LOGIN() AS original_login;
SELECT permission_name
FROM sys.fn_my_permissions(NULL, 'DATABASE')
ORDER BY permission_name;

Use the effective-permission query in the intended context during testing. Compare it with the application's documented operations. Catalog grants and real execution evidence answer related but different questions.

Make the Role Part of the Application Contract

Deliver the role definition, membership process, and test cases together. Document who approves changes and who owns the runtime account. Store credentials through the organization's approved mechanism rather than inside scripts.

Review access when new features or deployment methods arrive. A temporary grant needs a reason and a planned end. Least privilege remains useful only while the permissions continue to match the application's actual job.

Least privilege is not making access inconvenient, it is making authority intentional.

This post was rewritten from scratch in September 2026. The original, published on 2009-04-20, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.