GRANT (T-SQL)

Creates an entry in the security system that allows a user in the current database to work with data in the current database or execute specific Transact-SQL statements.

Syntax

Statement permissions:

GRANT {ALL | statement[,...n]}
TO security_account[,...n]

Object permissions:

GRANT
    {ALL [PRIVILEGES] | permission[,...n]}
    {
        [(column[,...n])] ON {table | view}
        | ON {table | view}[(column[,...n])]
        | ON {stored_procedure | extended_procedure}
    }
TO security_account[,...n]
[WITH GRANT OPTION]
[AS {group | role}]

Arguments

ALL

Specifies that all applicable permissions are being granted. For statement permissions, ALL can be used only by members of the sysadmin role. For object permissions, ALL can be used by members of the sysadmin and db_owner roles, and database object owners.

statement

Is the statement for which permission is being granted. The statement list can include:

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

n

A placeholder indicating that the item can be repeated in a comma-separated list.

TO

Specifies the security account list.

security_account

Is the security account to which the permissions are applied. The security account can be a:

• Microsoft® SQL Server™ user.
• SQL Server role.
• Microsoft Windows NT® user.
• Windows NT group.

When a permission is granted to a SQL Server user or Windows NT user account, the specified security_account is the only account affected by the permission. If a permission is granted to a SQL Server role or a Windows NT group, the permission affects all users in the current database who are members of the group or role. If there are permission conflicts between a group or role and its members, the most restrictive permission (DENY) takes precedence. security_account must exist in the current database; permissions cannot be granted to a user, role, or group in another database, unless the user has already been created or given access to the current database.

Two special security accounts can be used with GRANT. Permissions granted to the public role are applied to all users in the database. Permissions granted to the guest user are used by all users who do not have a user account in the database.

When granting permissions to a Windows NT local or global group, specify the domain or computer name the group is defined on, followed by a backslash, then the group name. However, to grant permissions to a Windows NT built-in local group, specify BUILTIN instead of the domain or computer name.

PRIVILEGES

Is an optional keyword that can be included for SQL-92 compliance.

permission

Is an object permission that is being granted. When permissions are granted on a table or a view, the permission list can include one or more of these statements: SELECT, INSERT, DELETE, or UPDATE.

Object permissions granted on a table can also include REFERENCES, and object permissions granted on a procedure can include EXECUTE. When permissions are granted on columns, the permissions list can include SELECT or UPDATE. When permissions are granted on stored procedures, the permissions list can include only EXECUTE.

When a user adds a row to a table with a FOREIGN KEY constraint, or changes data in a column with a FOREIGN KEY constraint, SQL Server must validate the data in the column with the data referenced in the FOREIGN KEY constraint. If the user does not have SELECT permissions on the referenced column or table, the REFERENCES permission for the column must be granted to the user. The REFERENCES permission cannot be granted for a system table.

column

Is the name of a column in the current database for which permissions are being granted.

table

Is the name of the table in the current database for which permissions are being granted.

view

Is the name of the view in the current database for which permissions are being granted.

stored_procedure

Is the name of the stored procedure in the current database for which permissions are being granted.

extended_procedure

Is the name of the extended stored procedure for which permissions are being granted.

WITH GRANT OPTION

Specifies that the security_account is given the ability to grant the specified object permission to the other security accounts. The WITH GRANT OPTION clause is valid only with object permissions.

AS {group | role}

Specifies the optional name of the security account in the current database that has the authority to execute the GRANT statement. AS is used when permissions on an object are granted to a group or role, and the object permissions need to be further granted to users who are not members of the group or role. Because only a user, rather than a group or role, can execute a GRANT statement, a specific member of the group or role grants permissions on the object under the authority of the group or role.

Remarks

Cross-database permissions are not allowed; permissions can be granted only to users in the current database for objects and statements in the current database. If a user needs permissions to objects in another database, create the user account in the other database, or grant the user account access to the other database, as well as the current database.

Note System stored procedures are the exception because EXECUTE permissions are already granted to the public role, allowing everyone to execute them. However, after a system stored procedure is executed, it checks the user’s role membership. If the user is not a member of the appropriate fixed server or database role necessary to run the stored procedure, the stored procedure does not continue.

The REVOKE statement can be used to remove granted permissions, and the DENY statement can be used to prevent a user from gaining permissions through a GRANT to their user account.

A granted permission removes the denied or revoked permission at the level granted (user, group, or role). The same permission denied at another level such as group or role containing the user takes precedence. However, although the same permission revoked at another level still applies, it does not prevent the user from accessing the object.

If a user activates an application role, the effect of GRANT is null for any objects the user accesses using the application role. Therefore, although a user may be granted access to a specific object in the current database, if the user uses an application role that does not have access to the object, the user also does not have access while the application role is activated.

The sp_helprotect system stored procedure reports permissions on a database object or user.

Permissions

GRANT permissions depend on the statement permissions being granted and the object involved in the permissions. The members of the sysadmin role can grant any permissions in any database. Object owners can grant permissions for the objects they own. Members of the db_owner or db_securityadmin roles can grant any permissions on any statement or object in their database.

Statements that require permissions are those that add objects in the database or perform administrative activities with the database. Each statement that requires permissions has a certain set of roles that automatically have permissions to execute the statement. For example, the CREATE TABLE permission defaults to members of the sysadmin and db_owner and db_ddladmin roles. The permissions to execute the SELECT statement for a table default to the sysadmin and db_owner roles, and the owner of the object.

There are some Transact-SQL statements that cannot be granted as permissions; the ability to execute these statements require membership in a fixed role that has implied permissions to execute special statements. For example, to execute the SHUTDOWN statement, the user must be added as member of the serveradmin role.

Members of the dbcreator, processadmin, securityadmin, and serveradmin fixed server roles have permissions to execute only these Transact-SQL statements.

Note Members of the diskadmin and setupadmin fixed server roles do not have permissions to execute any Transact-SQL statements, only certain system stored procedures. Members of the sysadmin fixed server role, however, have permissions to execute all Transact-SQL statements.

Members of the following fixed database roles have permissions to execute the specified Transact-SQL statements.

Note Members of the db_accessadmin fixed database role do not have permissions to execute any Transact-SQL statements, only certain system stored procedures.

The Transact-SQL statements that do not require permissions to be executed (automatically granted to public) are:

For more information about the permissions required to execute the system stored procedures, see the appropriate system stored procedure.

Examples

A. Grant statement permissions

This example grants multiple statement permissions to the users Mary and John, and the Corporate\BobJ Windows NT group.

GRANT CREATE DATABASE, CREATE TABLE

TO Mary, John, [Corporate\BobJ]

B. Grant object permissions within the permission hierarchy

This example shows the preferred ordering of permissions. First, SELECT permissions are granted to the public role. Then, specific permissions are granted to users Mary, John, and Tom. These users then have all permissions to the authors table.

USE pubs

GO

GRANT SELECT

ON authors

TO public

GO

GRANT INSERT, UPDATE, DELETE

ON authors

TO Mary, John, Tom

GO

C. Grant permissions to a SQL Server role

This example grants CREATE TABLE permissions to all members of the Accounting role.

GRANT CREATE TABLE TO Accounting

D. Grant permissions using the AS option

The Plan_Data table is owned by the user Jean. Jean grants SELECT permissions, specifying the WITH GRANT OPTION clause, on Plan_Data to the Accounting role. The user Jill, who is member of Accounting, wants to grant SELECT permissions on the Plan_Data table to the user Jack, who is not a member of Accounting.

Because the permission to GRANT other users SELECT permissions to the Plan_Data table were granted to the Accounting role and not Jill explicitly, Jill cannot grant permissions for the table based on the permissions granted through being a member of the Accounting role. Jill must use the AS clause to assume the grant permissions of the Accounting role.

/* User Jean */

GRANT SELECT ON Plan_Data TO Accounting WITH GRANT OPTION

/* User Jill */

GRANT SELECT ON Plan_Data TO Jack AS Accounting

See Also