Views can depend on other views and/or tables. Procedures can depend on other procedures, views, and/or tables. These dependencies can be thought of as an ownership chain.
Typically, the owner of a view also owns its underlying objects (other views and tables), and the owner of a stored procedure often owns all the procedures, tables, and views that the procedure references. Also, a view and its underlying objects are usually all in the same database, as are a stored procedure and all the objects it references.
For efficiency, when a user with permission to access a view does so, 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.
Similarly, if the same user owns a stored procedure and all the views or tables it references, and if the procedure and the objects it references are all in the same database, SQL Server checks only the permissions on the procedure.
However, if the ownership chain of a procedure or view is broken – that is, if 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 who is authorized to access it.
Ordinarily, a user who creates a view only has to grant permissions on that view. For example, say 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 will let Sue access it without checking permissions on authors.
However, 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 those other owners.
For example, Joe creates a view called auview2, which depends on Mary's view auview1. Joe grants Sue permission to use auview2. The object owner is given in parentheses after the object name:
SQL Server checks the permissions on auview2 and auview1 and finds that Sue is allowed to use them.
SQL Server performs no authorization checks at the time the view is created but performs authorization checks when the view is used. In fact, if Joe has permission on the CREATE VIEW statement, he can define a view based on the authors table even if he does not have SELECT permission on authors. However, the view would be useless to everyone, including Joe.
Taking this example a step further, suppose that Joe's view, auview2, depends on auview1, which depends on authors. Mary decides that she likes Joe's auview2 and creates auview3 on top of it. As you may recall, both auview1 and authors are owned by Mary. The situation can be depicted like this, with the object owner given in parentheses after the object name:
When Sue tries to access auview3, SQL Server checks permissions on auview3, auview2, and auview1. If Joe has granted Sue access on auview2 and Mary has granted her permission on auview3 and auview1, SQL Server allows the access. SQL Server checks permissions only if the object immediately before it in the chain has a different owner (or if it is the first object in the chain). For example, it checks auview2 because the object before it – auview3 – is owned by a different user. It does not check permission on authors because the object which immediately depends on it, auview1, is owned by the same user.
Procedures follow the same rules. As an example, suppose the situation is this:
To execute P4, Sue must have permission to execute P4, P2, and P1. Permission to execute P3 is not necessary, since P3 and P4 have the same owner.
SQL Server checks Sue's permissions on P4 and all objects it references each time she executes P4. However, SQL Server knows which referenced objects to check because it has determined this the first time Sue executes P4 and has saved this information with the procedure's execution plan. Unless one of the objects referenced by the procedure is dropped or otherwise redefined, SQL Server sticks with its initial decision about which objects to check.
The following illustration shows a situation in which procedures and views owned by two different users depend on each other in a fairly complex way.
If a user tries to execute procedure1, SQL Server checks permissions on procedure1, procedure3, view2, table2, and table3.
In summary, the purpose of this permission hierarchy is to allow every object's owner to fully control access to the object. Owners can control access to views and stored procedures, as well as to tables.