Microsoft® SQL Server® 2008 Bible

Chapter 50. Authorizing Securables

IN THIS CHAPTER

• Object ownership

• Securables permissions

• Object security

• Views and security

This chapter adds another important piece to the SQL Server security puzzle—securables. These are objects (for example, tables, views, stored procedures, columns) that can be secured in order to prevent unauthorized access.

Object Ownership

A very 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 being 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, then 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 that is contained by it has the same owner.

Most security management can be performed in Management Studio. With code, security is managed by means of the GRANT, REVOKE, and DENY Data Control Language (DCL) commands and several system stored procedures.

Object Security

If a ...