What’s ownership chaining?

Edit: 04-27-2009 K. Brian Kelley corrrectly pointed out in a comment that objects can still be owned by users in 2005 and later.  See his blog post to see how.

Edit:  While the code section is not as nice as I would like it is better.  Thanks for sticking with me.

I recently answered a question on SSC about how you granting execute rights on a stored procedure allows you to access tables without having any permissions granted on the tables.  The way that SQL Server implements this it through ownership chaining.  Here is the BOL (SQL Server 2005) definition of Ownership Chains:

When multiple database objects access each other sequentially, the sequence is known as a chain. Although such chains do not independently exist, when SQL Server 2005 traverses the links in a chain, SQL Server evaluates permissions on the constituent objects differently than it would if it were accessing the objects separately. These differences have important implications for managing security.
Ownership chaining enables managing access to multiple objects, such as multiple tables, by setting permissions on one object, such as a view. Ownership chaining also offers a slight performance advantage in scenarios that allow for skipping permission checks.

How Permissions Are Checked in a Chain

When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

Prior to SQL Server 2005, objects were owned by users, and, in most cases, designers and developers used the dbo user to own common objects.  In my experience it was very rare for objects to be owned by a “real” user. Thus if the customers table and orders table are both owned by dbo, then you can create a customer_orders_get stored procedure owned by dbo, grant user Joe execute permissions on the stored procedure, and Joe would be able to successfully use the stored procedure to query the customers and orders tables without having select permissions on the tables.  This is pretty easy to understand, as long all the objects are dbo.object_name I can access them using ownership chaining.
SQL Server 2005 introduced schemas which removed the use of users as owners.  Now objects belong to schemas, not users.  From BOL:

Beginning in SQL Server 2005, each object belongs to a database schema. A database schema is a distinct namespace that is separate from a database user. You can think of a schema as a container of objects. Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

The question now is, how does ownership chaining work when using schemas?  As the last line of the BOL quote states, a schema is owned as well, and ownership chaining in SQL Server 2005 and greater is based on schema ownership.  So if schema A is owned by dbo then you would be able to use ownership chaining to access objects in dbo from schema A and vice versa.  I think the best way to explain is with an example:

USE [master]
GO

-- drop the DB if it exists
IF EXISTS ( SELECT
name
FROM
sys.databases
WHERE
name = N'oc_test' ) 
DROP DATABASE [oc_test]
GO

USE [master]
GO

-- create the test database
CREATE DATABASE [oc_test] ON PRIMARY 
( 
NAME = N'oc_test', 
FILENAME = N'C:\Data\oc_test.mdf' , 
SIZE = 194560KB, 
MAXSIZE = 1024000KB, 
FILEGROWTH = 10240KB 
)
LOG ON 
( 
NAME = N'oc_test_log', 
FILENAME = N'C:\Logs\oc_test_log.ldf', 
SIZE = 204800KB, 
MAXSIZE = 204800KB, 
FILEGROWTH = 10240KB 
)
GO

-- switch to the new databse
USE oc_test ; 

GO

-- drop the user if it exists
IF EXISTS ( SELECT
1
FROM
sys.database_principals AS DP
WHERE
DP.[name] = 'test_user' ) 
BEGIN
DROP USER test_user ;
END

-- create our test user    
CREATE USER test_user WITHOUT LOGIN 
WITH DEFAULT_SCHEMA = dbo ;
GO

-- drop proc and schema
IF OBJECT_ID('ownership_chaining.table_one_ins') 
IS NOT NULL 
BEGIN
DROP PROCEDURE ownership_chaining.table_one_ins ;
END

GO

IF SCHEMA_ID('ownership_chaining') IS NOT NULL 
BEGIN
DROP SCHEMA ownership_chaining ;
END
GO

-- create our test schema, notice it is "owned" by dbo
CREATE SCHEMA ownership_chaining AUTHORIZATION dbo ;

GO

-- drop our test table if it exists
IF OBJECT_ID('dbo.table_one') IS NOT NULL 
BEGIN
DROP TABLE dbo.table_one ;
END

/*
create a test table NOTICE it is in the dbo 
schema which is owned by dbo
*/
CREATE TABLE dbo.table_one
(
column_one INT IDENTITY(1, 1),
column_two VARCHAR(10) NOT NULL
) ;

GO

/* 
Create the test procedure in the 
ownership_chaining schema which 
is owned by dbo
*/    
CREATE PROCEDURE ownership_chaining.table_one_ins
@column_two VARCHAR(10)
AS 
INSERT INTO
dbo.table_one (column_two)
VALUES
(@column_two) ;

RETURN ;

GO

/*
give out test user execut permissions on the 
procedure
*/
GRANT EXECUTE ON ownership_chaining.table_one_ins 
TO test_user ;

GO

-- change user context
EXECUTE AS USER = 'test_user' ;

-- execute the procedure
EXEC ownership_chaining.table_one_ins 
@column_two = 'Test 1' ;

-- go back to my login
Revert ;

-- verify that it worked which it should
SELECT
*
FROM
dbo.table_one AS _TO ;

/*
Now change the owner of the ownership_chaining 
schema so that it is no longer owned by dbo 
thus breaking the chain
*/
ALTER AUTHORIZATION ON SCHEMA::ownership_chaining 
TO db_owner ;

-- change user context
EXECUTE AS USER = 'test_user' ;

/* 
using try - catch so I get the revert as 
I expect failure
*/
BEGIN TRY 
EXEC ownership_chaining.table_one_ins 
@column_two = 'Test 1' ;
END TRY 
BEGIN CATCH
-- show the error message
SELECT
ERROR_MESSAGE() AS error ;

-- return to my user
revert ;
END CATCH 

-- verify data
SELECT
*
FROM
dbo.table_one AS _TO ;

I hope this helps clear up what I found to be a somewhat confusing topic.