Henry_Lee (5/10/2010)
I do not like different schemas within the same database, since they break the ownership chain. Joining different schemas in a proc forces me to grant permissions on the underlying objects rather than just on the proc.
Unless you're using SQL 2000 (where we really shouldn't be talking about using Schemas as they aren't fully implemented) I have to disagree with your assertion.
When you use schemas for logical separation in SQL Server 2005+ you don't have to have different owners on the schemas. If both schemas are owned by DBO then you get ownership chaining just like if all your SPROCs were in the same schema, but you can organize your tables into logical groups.
Personally I like to put all of my tables in a set of schemas and my SPROCs in a different schema (all owned by dbo). Then I just GRANT EXECUTE ON SCHEMA::MySchema and voila, no permissions chaining problems, execute permissions easily granted, and I don't have to worry about permissions leaking for all of the system SPROCs in DBO.
-DW