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.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.
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.
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.
