Grant and Revoke Commands in SQL Server 2008
In this article i described grant and revoke Data Control Language Command.
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:
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:
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: