SQL - Object Ownership and Chaining

I’m studying for the SQL 2008 Database Development exam, and of course learning all sorts of interesting stuff.

Object ownership and permissions got more complicated with the introduction of schemas in SQL 2005. I stumbled over one of the practice questions dealing with permissions; it set up a scenario with a procedure which selects from a table, and asked which permissions were needed to run the select procedure. The answer: both EXECUTE on the procedure and SELECT on the table.

Say what? How have I never had to grant permissions on the underlying objects? After much searching I found this in MSDN:

Regardless of the execution context that is specified in the module, the following actions always apply:

• When the module is executed, the Database Engine first verifies that the user executing the module has EXECUTE permission on the module.
• Ownership chaining rules continue to apply. This means if the owners of the calling and called objects are the same, no permissions are checked on the underlying objects.

Typically your objects are owned by dbo, so you don’t have to worry about permissions on underlying objects.

The ALTER AUTHORIZATION statement can be used to change ownership. But how do you determine the owner of an object? Interestingly, this isn’t shown in the SQL Management Studio dialogs. You’ll need to query the system tables to find the owner:

select DP.name, DP.type, DP.type_desc from sys.objects SO
join sys.database_principals DP on DP.principal_id = SO.principal_id
where SO.name = 'TestTable'