Object Ownership
An important aspect of SQL Server's security model involves object ownership. Every object is contained by a schema. The default schema is dbo — not to be confused with the dbo role.
Ownership becomes critical when permission is granted to a user to run a stored procedure when the user doesn't have permission to the underlying tables. If the ownership chain from the tables to the stored procedure is consistent, then the user can access the stored procedure and the stored procedure can access the tables as its owner. However, if the ownership chain is broken, meaning there's a different owner somewhere between the stored procedure and the table, the user must have rights to the stored procedure, the underlying tables, and every other object in between.
There is a fine point in the details: A schema is owned. And because a schema is owned, anything contained by it has the same owner.
Get Microsoft SQL Server 2012 Bible now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
