Managing Permissions

When users connect to Microsoft® SQL Server™, the activities they can perform are determined by the permissions granted to their security accounts, Microsoft Windows NT® groups, or role hierarchies to which their security accounts belong. The user must have the appropriate permissions to perform any activity that involves changing the database definition or accessing data. Some activities, such as those that do not change items in the database or access data, do not require permissions.

Object Permissions

Working with data or executing a procedure requires a class of permissions known as object permissions. Object permissions are based on a table, view, or stored procedure and control the ability to execute the SELECT, INSERT, UPDATE, and DELETE statements against the table or view, or the EXECUTE permission on a stored procedure. For example, if a user needs to be able to retrieve data from an entire table, then the SELECT object permission should be granted to that user.

Object permissions are:

SELECT, INSERT, UPDATE, and DELETE statement permissions, which can be applied to the entire table and view.
SELECT and UPDATE statement permissions, which can be selectively applied to individual columns of a table or view.
INSERT and DELETE statement permissions, which affect the entire row, and therefore can be applied only to the table and view and not to individual columns.
EXECUTE statement permissions, which affect only stored procedures.

Statement Permissions

Activities involved with creating a database or an item in a database, such as a table or stored procedure, require a different class of permissions called statement permissions. For example, if a user must be able to create a table within a database, then the CREATE TABLE statement permission should be granted to the user. Statement permissions, such as CREATE DATABASE, are applied to the statement itself, rather than to a specific object defined in the database.

Statement permissions are:

CREATE DATABASE	CREATE DEFAULT
CREATE PROCEDURE	CREATE RULE
CREATE TABLE	CREATE VIEW
BACKUP DATABASE	BACKUP LOG

Implied Permissions

The last class of activities controlled by permissions in SQL Server are those activities that can be performed only by members of predefined system roles or owners of database objects. For example, a member of the sysadmin fixed server role automatically inherits full permission to do or see anything in a SQL Server installation. The sysadmin role has permissions that cannot be changed and implied permissions, such as the ability to configure the SQL Server installation, that cannot be applied to other user accounts.

Database object owners also have implied permissions allowing them to perform all activities with the object they own. For example, a user who owns a table can view, add, or delete data, alter the table definition, or control permissions that allow other users to work with the table.