Microsoft SQL Server's Security Model

Permissions

A permission allows someone to do something within a database. There are two types of permissions: object and statement. Object permissions control who can access and manipulate data in tables and views and who can run stored procedures. Statement permissions control who can drop and create objects within a database.

SQL Server uses the commands GRANT, REVOKE, and DENY to manage permissions.

GRANT - When you GRANT a permission to an object, you allow someone to perform an action against the object (for example, SELECT, UPDATE, INSERT, DELETE, or EXECUTE). When you GRANT permission to a statement, you allow someone to run the statement (for example, CREATE TABLE).

REVOKE - When you REVOKE a permission from an object, you prevent someone from performing an action against the object (for example, SELECT, UPDATE, INSERT, DELETE, or EXECUTE). When you REVOKE permission from a statement, you take away a user's ability to run the statement (for example, CREATE TABLE).

• Post a comment
• Email Article
• Print Article
• Share Articles
  • Digg
  • del.icio.us
  • Slashdot
  • DZone
  • Reddit
  • StumbleUpon
  • Facebook
  • FriendFeed
  • Furl
  • Newsvine
  • Google
  • LinkedIn
  • MySpace
  • Technorati
  • Twitter
  • Windows Live
  • YahooBuzz

DENY - When you DENY a permission from an object, you explicitly prevent someone from using the permission (for example, SELECT, UPDATE, INSERT, DELETE, or EXECUTE), whereas REVOKE actually removes the permission.

Object Permissions

Object permissions control access to objects within SQL Server. You can grant and revoke permissions to tables, table columns, views, and stored procedures through the Enterprise Manager or through system procedures. A user who wants to perform an action against an object must have the appropriate permission. For example, when a user wants to SELECT * FROM table1, she must have SELECT permission for the table. Table 1 summarizes the types of object permissions.

Table 1 - Summary of Object Permissions

Statement Permissions

Statement permissions control who can perform administrative actions such as creating or backing up a database. Only the sa, members of the sysadmin role, or database owner can administer statement permissions. I advise prudence in granting access to statement permissions such as CREATE DATABASE, BACKUP DATABASE, and BACKUP LOG. Usually, the best approach is to let the sa, a member of the sysadmin role, or the database owner manage these statements. Following is a list of statement permissions that can be granted or revoked:

• CREATE DATABASE - Creates a database. This permission can be granted only by the sa and only to users in the master database.
• CREATE DEFAULT - Creates a default value for a table column.
• CREATE PROCEDURE - Creates a stored procedure.
• CREATE RULE - Creates a table column rule.
• CREATE TABLE - Creates a table.
• CREATE VIEW - Creates a view.
• BACKUP DATABASE - Backs up the database.
• BACKUP TRANSACTION - Backs up the transaction log.

Roles

Roles provide a logical way to group users with permissions. The following are the types of roles found in SQL Server:

• Server roles
• Database roles

Server Roles

Server roles provide levels of access to server operations and tasks. If an individual is placed in a certain role, he can perform the function permitted by the role. For example, an individual who is member of the sysadmin role can perform any type of action in SQL Server.

Server roles are predefined and are serverwide. These roles are not database specific and cannot be customized. Table 2 provides a listing and explanation for each type of server role.

Table 2 - Server Roles

Database Roles

Database roles provide the assignment of a set of database-specific permissions to an individual or a group of users. Database roles can be assigned to NT Authenticated logins or SQL Server Authenticated logins. Roles that are assigned to NT Authenticated logins can be assigned to NT users and NT groups. Roles can also be nested so that a hierarchical group of permissions can be assigned to logins.

Database roles are database specific. SQL Server provides three types of roles:

• Predefined database roles
• User-defined database roles
• Implicit roles

Predefined Database Roles

Predefined database roles are standard SQL Server database roles. Each database in SQL Server has these roles. Predefined database roles make it easy to delegate responsibility. For example, a developer might be assigned the db_ddladmin role in a development database. This role would allow a developer to create and drop objects (tables, stored procedures, views, and so on) on an as-needed basis.

Predefined database roles are database specific and cannot be customized. Table 3 provides a description of each predefined database role.

Table 3 - Predefined Database Roles