The following questions are intended to
Logins grant access to the SQL Server. Users and roles grant access to a database. This is analogous to using your cash card and PIN to gain access to an ATM. Before you can withdraw cash from your account, the bank will check to see that you have sufficient funds and that you are not trying to withdraw more than a daily limit. In the same way, every time you attempt to execute statements or use objects in the database, SQL Server will check that you have permission to perform these operations.
Permissions are granted to or
To complete the lessons in this chapter, you must have
You must also have done the following:
To allow a user to access or create objects in SQL Server, that
After this lesson, you will be able to
- Describe the three different types of permissions in SQL Server
Estimated lesson time: 15 minutes
There are three types of permissions in SQL Server: statement, object, and
|Permission type||Permission||Applies to|
REFERENCES (DRI in SQL Server Enterprise Manager)
Tables, views, and
Tables and views
Tables and views
Tables, views, and columns
Tables and columns
Depends on role
The owned object
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
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'
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).
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.
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.
Members of fixed roles and
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
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.
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.