Grant and Revoke Commands in SQL Server 2008

In this article i described grant and revoke Data Control Language Command.
  • 5612

Introduction

In this article I describe Grant and Revoke Data Control language command. These commands are used to provide access permissions for security within database which are shared by multiple users.

First of all we create a table:

create database EmployeeDetails  --to create database
go
use EmployeeDetails  --to enter within database
go
create table emp (empId int, empName varchar(15)) --creation of database
go
insert into emp --insertion of data
select 1,'d'union all
select 2,'e'union all
select 3,'f'union all
select 4,'g'union all
select 5,'h'
go
select * from emp --showing table

Output:

CreateTable_SQL.jpg

Grant Command

Grant command is used to provide privileges like select, all, execute etc on the database objects to the users.

Syntax

GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];

where,

privilege_name: is the access rights granted to user.
object_name: database, table or stored procedure name.
user_name: username to whom access rights are granted.
public: access rights are granted to all users.
with grant option: user can grant access rights to other users.

Example

grant select --granting select privilege
on emp
to public --you can give username at the place of public

This will grant access rights i.e Select permission on emp table to all the users. You can provide username in place of public to grant Select permission on emp table. But user can not grant access rights to other users because [with grant option] is not used.

Output:

GrantCommand_SQL.jpg

Revoke command:

Revoke command is used to remove the permission from the user within the database.

Syntax

revoke privilege_name
on object_name
from {user_name|public|role_name}

where privileges are the access rights granted to the user while role are a collection of privileges.

Example:

revoke select--removing select privilege
on emp
from public

This will revoke a access rights for select permission on emp table from all users (public keyword is used).

Output:

RevokeCommand_SQL.jpg

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.