One of the things that we have to re-learn when going from SQL Server 2000 to 2005/2008 is that objects no longer have owners. Rather, objects are contained in schema and schema have owners. And if you query sys.objects, you will see that this seems to hold true. While there is a principal_id column, normally the value is NULL for all objects. I was looking at it the other day and first wondering why there was a principal_id column. I took a close look at the text for sys.objects in Books Online and it says this:

ID of the individual owner, if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership. 

In other words, objects can still be owned by a user. But the text "alternate owner" made me pause. What exactly did that mean? Well, let's create a test scenario. We'll need two objects in different schema. Those schema will need different owners. And of course, we're going to need a test subject, so a third user will be good. Let's get that set up:

/* Set up the users needed to show the issue.
  Do this in a "play" database. */
CREATE USER User1 WITHOUT LOGIN;
GO

CREATE USER User2 WITHOUT LOGIN;
GO

CREATE USER User3 WITHOUT LOGIN;
GO

/* Create the different schema and set them to
   be owned by different users. */
CREATE SCHEMA User1 AUTHORIZATION User1;
GO

CREATE SCHEMA User2 AUTHORIZATION User2;
GO

/* Verify the schema are owned by different users. */
SELECT schema_id, [name], USER_NAME(principal_id) [Owner]
FROM sys.schemas
WHERE LEFT([name], 4) = 'User';
GO


Now that we have the user and the schema, let's create a table and a stored procedure that refers to the table. They need to be in the separate schema we created. This should mean there is no ownership chain:

/* Create the table that will be reference in schema User1. */
CREATE TABLE User1.MyTable (Number INT);
GO

INSERT INTO User1.MyTable (Number) VALUES (1);
INSERT INTO User1.MyTable (Number) VALUES (2);
INSERT INTO User1.MyTable (Number) VALUES (3);
GO

/* Create the referencing stored procedure in a different
  schema, User2. Since the schema User1 and User2 are
  owned by different users, typically an ownership chain
  wouldn't be formed. */
CREATE PROC User2.MyProc
AS
BEGIN
  SELECT Number FROM User1.MyTable;
END;
GO

/* Grant User3, our guinea pig user, the ability to execute
   the referencing stored procedure. User3 has no other 
   rights, especially no rights on User1.MyTable. */
GRANT EXECUTE ON OBJECT::User2.MyProc TO User3;
GO

/* Verify the objects we just created and the schema and
 "owners." Note the Owner column shows "--No Owner--." */
SELECT 
  OBJECT_ID, SCHEMA_NAME(schema_id)[Schema], [name],
  COALESCE(USER_NAME(principal_id),'--No Owner--') [Owner] 
FROM sys.objects
WHERE LEFT([name], 2) = 'My';
GO

Now to test to see if User3 can successfully execute the stored procedure.

/* Execute the stored procedure in the context of User3.
   This should generate an error indicating User3 doesn't
   have SELECT rights against User1.MyTable. */
EXECUTE AS USER = 'User3';
GO

EXEC User2.MyProc;
GO

REVERT;
GO 

The error tells us an ownership chain isn't forming. This is the expected behavior. But what if we use ALTER AUTHORIZATION against User1.MyTable? What then?

/* Now let's add an owner to User1.MyTable. That owner
   will be User2. */
ALTER AUTHORIZATION ON OBJECT::User1.MyTable TO User2;
GO 

/* Re-query the schema to show ownership hasn't changed
   on either of them. */
SELECT schema_id, [name], USER_NAME(principal_id) [Owner] 
FROM sys.schemas
WHERE LEFT([name],4)='User';
GO 

/* Re-query on the objects. Note the Owner now present
   for User1.MyTable. It's User2, meaning an ownership
   chain can form. */
SELECT OBJECT_ID, SCHEMA_NAME(schema_id)[Schema], [name],
       COALESCE(USER_NAME(principal_id), '--No Owner--') [Owner]
FROM sys.objects
WHERE LEFT([name], 2) = 'My';
GO 


And when we execute the stored procedure, it now works. The ownership chain formed.

/* Re-execute the stored procedure as User3 and this time
   it works. The ownership chain did form. */
EXECUTE AS USER = 'User3';
GO

EXEC User2.MyProc;
GO

REVERT;
GO


But here's an interesting consideration: what about other objects in the User1 schema? Can they form an ownership chain with the table? The answer is no, as this script will demonstrate.

/* Let's now create a stored procedure in the User1 schema
   and see if we have an ownership chain. */
CREATE PROC User1.AnotherProc
AS
BEGIN
  SELECT Number FROM User1.MyTable;
END;
GO

/* Grant the ability to execute the stored procedure to our
   guinea pig user again so we can test. */
GRANT EXECUTE ON OBJECT::User1.AnotherProc TO User3;
GO

/* And when we do execute the stored procedure, we get the
   SELECT permission was denied error again. */
EXECUTE AS USER = 'User3';
GO

EXEC User1.AnotherProc;
GO

REVERT;
GO

To be blunt, I don't like this capability at all. The ability to do this isn't really talked about a lot in Books Online and I know in the 2005 version there was a lot of confusion when object owner was referenced. When you consider the following, it makes sense: the object owner is, by default, the owner of the schema. If no owner is actually specified for the object, the schema owner is the assumed owner. But you can have an object owner.

This can lead to confusion where there shouldn't be any. What this means, from a security perspective, is we need to check at both the object and the schema level for ownership and consider ownership chaining from there. Also, it poses a real potential for issues, especially if someone does a "one-off" and implements a specific owner on an object trying to make an ownership chain work. I would have preferred that they just left this at the schema level and been done with it. But it's not that way, meaning we've got to be aware of that as we look at database security, especially with respect to ownership chains.