Microsoft SQL Server 2012 Bible

Permission Chains

In SQL Server databases, users often access data by going through one or several objects. Ownership chains apply to views, stored procedures, and user-defined functions, for example:

• A program might call a stored procedure that then selects data from a table.
• A report might select from a view, which then selects from a table.
• A complex stored procedure might call several other stored procedures.

In these cases, the user must have permission to execute the stored procedure or select from the view. Whether the user also needs permission to select from the underlying tables depends on the ownership chain from the object the user called to the underlying tables.

If the ownership chain is unbroken from the stored procedure to the underlying tables, the stored procedure can execute using the permission of its owner. The user needs permission only to execute the stored procedure. The stored procedure can use its owner's permission to access the underlying tables. The user doesn't require permission to the underlying tables.

Ownership chains are great for developing tight security in which users execute stored procedures but aren't granted direct permission to any tables.

If the ownership chain is broken, meaning that there's a different owner between an object and the next lower object, then SQL Server checks the user's permission for every object accessed.

The EXECUTE AS clause, added in SQL Server 2005, adds flexibility to control effective permissions under which ...