SQL Commands to Set Permissions

The following SQL keywords are used to change or set someone’s permissions:

1. GRANT

2. DENY

3. REVOKE.


GRANT:

GRANT keyword is used to set the permission to particular user. The permissions may include database, create table, create view etc.

Example

Use TESTDB
GRANT CREATE TABLE TO ALL; --Allows all users to create table in TESTDB Data base.
GO


DENY:

Denies a permission to a user. The permissions may include database, create table, create view etc.

Example

Use TESTDB
DENY CREATE TABLE TO USER1; --Denies permission on create table in TESTDB Data base to USER1.
GO

REVOKE:

The REVOKE keyword is used to remove a previously granted or denied permission.

Example

Use TESTDB
REVOKE GRANT OPTION FOR CREATE TABLE TO USER1; --Revokes the permission on create table in TESTDB Data base to USER1.
GO