Microsoft® SQL Server® 2012 Bible

841

CHAPTER

33

Authorizing Securables

IN THIS CHAPTER

Building Permission Chains

Understanding Object Ownership and Security

Using Fixed and User-defi ned Roles

Managing Views and Security

T

his chapter adds another important piece to the SQL Server security puzzle — securables.

They include server and objects including databases, tables, and views that you can secure to

prevent unauthorized access.

You can perform most security management in SQL Server Management Studio using T-SQL code to

grant, revoke, and denyaccess, through the Data Control Language (DCL) commands and several

system-stored procedures.

Permission Chains

In SQL Server databases, users often access data by going through one or several objects. Ownership

chains apply to views, stored procedures, and user-defi ned functions, for example:

■ (^) A program might call a stored procedure that then selects data from a table.
■ A report might select from a view, which then selects from a table.
■ (^) A complex stored procedure might call several other stored procedures.
In these cases, the user must have permission to execute the stored procedure or select from the
view. Whether the user also needs permission to select from the underlying tables depends on the
ownership chain from the object the user called to the underlying tables.
If the ownership chain is unbroken from the stored procedure to the underlying tables, the stored
procedure can execute using the permission of its owner. The user needs permission only to execute
the stored procedure. The stored procedure can use its owner’s permission to access the underlying
tables. The user doesn’t require permission to the underlying tables.
c33.indd 841c33.indd 841 7/31/2012 10:01:22 AM7/31/2012 10:01:22 AM
http://www.it-ebooks.info