DEV Community

facilishz
facilishz

Posted on

SQL Server EXECUTE AS and the Ownership Chain Behind a Stored Procedure

#cybersecurity #securityengineering

SQL Server EXECUTE AS and the Ownership Chain Behind a Stored Procedure

A stored procedure can give a caller access to tables without granting direct table permissions. This is routine SQL Server design, but the effective authority can be difficult to see from a user's role membership. The module execution context and the ownership chain decide which permissions the engine evaluates.

Two related mechanisms

SQL Server can use an ownership chain when a module and the objects it references have the same owner. The caller needs permission on the module, and the engine can avoid a second permission check on an object later in the chain. This behavior supports encapsulated data access and is documented through the Database Engine permission model.

EXECUTE AS changes the execution context for a user-defined module. CALLER evaluates permissions as the person invoking the module. SELF uses the module creator or alterer, and OWNER uses the current owner of the module or its schema. A named principal can also be specified. Microsoft's reference says additional permission checks on objects accessed by the module are then performed against the account in the EXECUTE AS clause.

CREATE OR ALTER PROCEDURE dbo.ReadPurchaseOrders
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    SELECT PurchaseOrderID, VendorID, TotalDue
    FROM Purchasing.PurchaseOrderHeader;
END;
Enter fullscreen mode Exit fullscreen mode

The example exposes a design question: what can the procedure owner read? If the owner is a highly privileged account or the database owner, the module may become a broad access path. The caller no longer needs direct table permission, so review must move to the code and the module identity.

Review the complete chain

Inventory procedures, functions, triggers, and queues that specify a non-caller context. Record the owner, execute_as_principal_id, referenced objects, dynamic SQL, cross-database calls, and any database trust relationship. Dynamic SQL can step outside the static ownership chain if it executes as another principal or targets another database.

Check who can create or alter the module, who owns the schema, and whether ownership changes over time. A schema owner with broad database rights may alter the effective authority of every module in that schema. Review IMPERSONATE permissions and explicit EXECUTE AS statements as well as module clauses.

Use ORIGINAL_LOGIN() and session context only as evidence, not as a substitute for a permission review. Test with low-privilege accounts and confirm that a caller cannot alter inputs to make the module perform a broader action. Include error paths and transaction handling.

Detection and response

Audit changes to module definition, owner, schema owner, impersonation grants, and cross-database trust. Alert on new EXECUTE AS clauses in sensitive schemas and on accounts that can alter them. Review failed permission checks and unusual calls to modules that return large result sets.

When investigating a suspected escalation, reconstruct the effective context for the exact module version. Compare the stored procedure definition with source control, check schema ownership, and inspect who used the module. Then revoke unnecessary impersonation, narrow the module identity, and retest the original business workflow.

Ownership chains and execution context are legitimate tools for reducing direct grants. They become risky when the delegated identity is broader than the task and no one reviews the code path that uses it.

References

Top comments (0)