7.3. Implement Permissions

It does not do you much good to go through the bother of authenticating someone if you are not going to use that information to restrict or permit their activities. In SQL Server, permissions granted to principals on securables are the basis for all access restrictions. SQL Server manages all permissions at every scope through three TSQL statements:

  • GRANT

  • DENY

  • REVOKE

7.3.1. Understand the Permissions Model

Whether you are using SSMS or TSQL to set permissions, the concepts are all the same. The functional permissions that are assigned to a principal are the aggregate permissions that are based on their association with multiple roles or permissions that may be assigned directly to the account.

The GRANT statement

Get SQL Server® 2008 Administration: Instant Reference now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.