Owning an Object in SQL Server 2005/2008

2009/03/04

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

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

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.