O'Reilly logo

Professional Microsoft® SQL Server® 2008 Administration by Steven Wort, Ross LoForte, Wayne Snyder, Ketan Patel, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required