Microsoft SQL Server 7.0 System Administration Training Kit
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.