Using Ownership Chains

Views and stored procedures provide a secondary method of giving users access to data and the ability to perform activities. They provide users with access to underlying items in the database and bypass the permissions defined directly for specific objects and statements.

Views can depend on other views or tables. Procedures can depend on other procedures, views, or tables. These dependencies can be thought of as an ownership chain. Ownership chains only apply to SELECT, INSERT, UPDATE, and DELETE statements.

Typically, the owner of a view also owns the underlying objects (other views or tables), and the owner of a stored procedure often owns all the referenced procedures, tables, and views. Also, views and underlying objects are usually all in the same database, as are stored procedures and all the objects referenced. When temporary objects are created within a stored procedure, they are owned by the procedure owner and not by the user currently executing the procedure.

When a user accesses a view, Microsoft® SQL Server™ does not check permissions on any of the view's underlying objects if these objects and the view are all owned by the same user, and if the view and all its underlying objects are in the same database. If the same user owns a stored procedure and all the views or tables it references, and if the procedure and objects are all in the same database, SQL Server checks only the permissions on the procedure.

If the ownership chain of a procedure or view is broken (not all the objects in the chain are owned by the same user), SQL Server checks permissions on each object in the chain whose next lower link is owned by a different user. In this way, SQL Server allows the owner of the original data to retain control over its accessibility.

Usually, a user who creates a view has to grant permissions only on that view. For example, Mary has created a view called auview1 on the authors table, which she also owns. If Mary grants Sue permission to use auview1, SQL Server allows Sue access to it without checking permissions on authors.

A user who creates a view or stored procedure that depends on an object owned by another user must be aware that any permissions he or she grants depend on the permissions allowed by the other owner.

For example, Joe creates a procedure called procedure1, which depends on procedure2 (also owned by Joe), and procedure3 (owned by Sue). These procedures in turn depend on other tables and views owned by Joe and Sue.

Joe grants Mary permission to use procedure1. SQL Server checks the permissions on procedure1, procedure3, view2, table2, and table3 to check that Mary is allowed to use them.