Lesson 1: Types of Permissions

[Previous] [Next]

To allow a user to access or create objects in SQL Server, that user must be granted permissions on the object. High-level users can be given permissions that allow them to develop objects in the database. Users can also be given permissions that allow them to access objects, such as the ability to select from a table. This lesson discusses the types of permissions in SQL Server.

After this lesson, you will be able to

• Describe the three different types of permissions in SQL Server

Estimated lesson time: 15 minutes

The Three Types of Permissions

There are three types of permissions in SQL Server: statement, object, and implied. The following table summarizes the SQL Server permissions, grouping them by type, and indicates which database or object the permission applies to.

Permission type	Permission	Applies to
Statement	CREATE DATABASE CREATE DEFAULT CREATE PROCEDURE CREATE RULE CREATE TABLE CREATE VIEW BACKUP DATABASE BACKUP LOG	master database All databases All databases All databases All databases All databases All databases All databases
Object	SELECT INSERT DELETE UPDATE REFERENCES (DRI in SQL Server Enterprise Manager) EXECUTE	Tables, views, and columns Tables and views Tables and views Tables, views, and columns Tables and columns Stored procedures
Implied	Fixed role Depends on role	Object owner The owned object

Statement Permissions

Activities that involve creating a database or items in a database require a class of permissions called statement permissions. These permissions give users the privilege of issuing certain Transact-SQL statements. Statement permissions, such as CREATE DATABASE, are applied to the statement itself, rather than to a specific item that is defined in the database. Only members of the sysadmin, db_owner, or db_securityadmin roles can grant statement permissions.

Object Permissions

Activities that involve working with data or executing procedures require a class of permissions known as object permissions.

Table and View Permissions

Object permissions for tables and views control users' abilities to gain access to data using the SELECT, INSERT, UPDATE, and DELETE statements against the table or view. Object permissions are therefore called SELECT, INSERT, UPDATE, and DELETE.

Using a WHERE clause in an UPDATE statement requires both SELECT and UPDATE permissions.

The REFERENCES Permission

Another object permission called REFERENCES applies to tables. When a user adds a row to a table or changes data in a table with a FOREIGN KEY constraint, SQL Server must validate the data in the table that is referenced in the FOREIGN KEY constraint. If the user does not have SELECT permissions on the referenced table, the REFERENCES permission for the table must be granted to the user.

In SQL Server Enterprise Manager, the REFERENCES permission is referred to as DRI (Declarative Referential Integrity).

Column Permissions

SELECT, UPDATE, and REFERENCES permissions can be applied selectively to individual columns. This means that rather than giving a user or role access to an entire table, you can grant access to certain columns of the table only. To work with column permissions, you must use Transact-SQL. SQL Server Enterprise Manager does not allow you to grant, revoke, or deny column permissions.

TIP

---

It is recommended that you use views rather than column permissions. Views are easier to manage and give better performance than using column permissions.

Stored Procedure Permissions

The EXECUTE permission is the only object permission for a stored procedure. This permission allows a user to execute the stored procedure.

Implied Permissions

Members of fixed roles and owners of database objects can perform certain activities apart from those governed by normal statement and object permissions. Permissions to perform these activities are called implied, predefined, or implicit permissions.

Fixed Role Permissions

Fixed roles have implied administrative permissions. For example, a user who is added as a member of the sysadmin role automatically inherits full permissions to do or read anything in a SQL Server installation. The sysadmin role has permissions that cannot be changed, as well as implied permissions that cannot be applied to other user accounts, such as the ability to configure the SQL Server installation.

Object Owner Permissions

Object owners also have implied permissions that allow them to perform all activities on objects that they own. For example, a user who is a table owner, or a member of a group that is designated as the table owner, can perform any activity that is related to the table. The user can view, add, or delete data, alter the table definition, and control the permissions that allow other users to work with the table.

TIP

---

It usually not a good idea to grant individual user accounts the ability to create objects. It adds a layer of complexity to your security model that is difficult to manage. A better idea is to use the implied permissions of roles such as db_owner and sysadmin so that all objects are owned by the same role.

Lesson Summary

Statement permissions are permissions to perform activities that involve creating a database or items in a database. Object permissions are permissions to perform activities that involve working with data or executing procedures. Members of fixed roles and owners of database objects can perform certain activities apart from those governed by normal statement and object permissions. Permissions to perform these activities are called implied, predefined, or implicit permissions.