Managing Permissions
When users connect to an instance of Microsoft® SQL Server™, the activities they can perform are determined by the permissions granted to:
- Their security accounts.
- The Microsoft Windows NT® 4.0 or Windows® 2000 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.
Managing permissions includes granting or revoking user rights to:
- Work with data and execute procedures (object permissions).
- Create a database or an item in the database (statement permissions).
- Utilize permissions granted to predefined roles (implied permissions).
Object Permissions
Working with data or executing a procedure requires a class of permissions known as object permissions:
- 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.
- SELECT permissions, which may be applied to user-defined functions.
- 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 stored procedures and functions.
Statement Permissions
Activities involved in 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 grant the CREATE TABLE statement permission 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:
- BACKUP DATABASE
- BACKUP LOG
- CREATE DATABASE
- CREATE DEFAULT
- CREATE FUNCTION
- CREATE PROCEDURE
- CREATE RULE
- CREATE TABLE
- CREATE VIEW
Implied Permissions
Implied permissions control 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 inherits automatically full permission to do or see anything in a SQL Server installation.
Database object owners also have implied permissions that allow 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.