Grants permissions on a securable to a principal.
The full syntax of the GRANT statement is complex. The syntax diagram above has been simplified to draw attention to its structure. Complete syntax for granting permissions on specific securables is described in the topics listed below.
The REVOKE statement can be used to remove granted permissions, and the DENY statement can be used to prevent a principal from gaining a specific permission through a GRANT.
Granting a permission removes DENY or REVOKE of that permission on the specified securable. If the same permission is denied at a higher scope that contains the securable, the DENY takes precedence. But revoking the granted permission at a higher scope does not take precedence.
Database-level permissions are granted within the scope of the specified 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.
A table-level DENY does not take precedence over a column-level GRANT. This inconsistency in the permissions hierarchy has been preserved for the sake of backward compatibility. It will be removed in a future release.
The sp_helprotect system stored procedure reports permissions on a database-level securable.
The grantor (or the principal specified with the AS option) must have either the permission itself with GRANT OPTION, or a higher permission that implies the permission being granted. If using the AS option, additional requirements apply. See the securable-specific topic for details.
Object owners can grant permissions on the objects they own. Principals with CONTROL permission on a securable can grant permission on that securable.
Grantees of CONTROL SERVER permission, such as members of the sysadmin fixed server role, can grant any permission on any securable in the server. Grantees of CONTROL permission on a database, such as members of the db_owner fixed database role, can grant any permission on any securable in the database. Grantees of CONTROL permission on a schema can grant any permission on any object within the schema.
See the following topics for securable-specific syntax.
Remote Service Binding
XML Schema Collection