Professional Microsoft® SQL Server® 2008 Administration

9.7. Securables and Object Permissions

We've touched on permissions several times in this chapter, but until now we've only scratched the surface. Let's look at SQL Server 2008's permission model in more detail. Starting with SQL Server 2005, the security model introduced the concepts of securables and scopes. Quite simply, a securable is any object you can assign permissions against. A scope is a securable that can contain other securables. For instance, the server securable corresponds to the SQL Server itself. It's a scope, too, though, because it contains endpoint, login, and database securables.

9.7.1. Ownership Chaining

If you look at some of the more basic securables, such as tables, views, and stored procedures, there's a security feature called ownership chaining that you need to be aware of. If one object refers to a second object, such as a stored procedure issuing a SELECT statement against a table, and both objects are in the same schema, an ownership chain is formed. When an ownership chain is in place, SQL Server will not check the security on the object being referred to. In other words, if the user is able to execute the stored procedure, SQL Server will not check to see whether the user can SELECT from the table when the user executes the stored procedure. SQL Server assumes that because both objects are in the same schema, the creator(s) intended for this type of access to happen from the stored procedure. If the user doesn't have SELECT permissions against ...