GRANT (TSQL Statement)

grants permission(s) ON the specified object TO the specified principal(s).

Syntax

GRANT <permission> [,... n]
    ON [schema_name .] object_name
    TO principal [,... n]

<permission> ::= SELECT | UPDATE | INSERT | DELETE

Arguments

<permission>

is a permission that can be granted on the object.

[schema_name.]object_name

is the object on which the permission(s) will be granted.

principal

is the name of a user or a role to which the permission(s) will be granted.

Remarks

Only sa or dbo is allowed to execute this statement.

To get access to an object, the user must have the required permissions on it. The user can have been granted these permissions directly, or it can benefits indirectly from permissions granted to a role it is a member of.

If the user has a deny permission on the object, directly or indirectly, the access will always be denied. A deny permission overrides all granted permissions.

To remove a granted permission, use REVOKE permissions.

The command SHOW PERM U displays the list of users and roles, and their permissions.

The command SHOW PERM T displays the list of tables, and the permissions on them.

Examples

CREATE TABLE clients(id INT NOT NULL IDENTITY(100, 1) PRIMARY KEY, name VARCHAR(20));
GO

INSERT INTO clients VALUES('Simpson');
GO

CREATE LOGIN igor  WITH PASSWORD = 'Adjl6745dfzt';
CREATE LOGIN alice WITH PASSWORD = '9rTkfjj76589';

CREATE USER alice;
CREATE USER igor;

CREATE ROLE Sales;
ALTER ROLE Sales ADD MEMBER alice;
ALTER ROLE Sales ADD MEMBER igor;

GRANT SELECT ON clients TO Sales; -- Alice and Igor benefits from the SELECT permission granted to Sales
DENY SELECT ON clients TO igor;   -- but Igor has a "deny SELECT" permission
GO

SELECT * FROM clients; -- Alice can execute this statement but not Igor, as he has a "deny SELECT" permission