Role in SQL Server

Introduction

In a multi-user database server there are many users accessing the data. In that type of database security is a major problem for the administrator. To solve this we use "Role" to provide the permission and deny the permission to the user for accessing the data in the database.

 

In this article I describe Role. I assume you are familiar with grant and revoke DCL Commands in SQL Server, for more help you can visit Grant and Revoke DCL Command in SQL Server.

 

Roles

 

Roles are a collection of permissions or access rights for providing permissions or privileges easily in a large database system with multiple users.

 

First of all we create a table on which we create the role.

 

Creation of table

 

Use the following command to create the table:

create table emp(empId int,empName varchar(15))

Insertion Of Data

 

Use the following command to insert data in the table:

insert into emp
select 1,'d'union all
select 2,'e'union all
select 3,'f'union all
select 4,'g'

Output

Use the following command to see the output of the table:

select * from emp

Creation of role

We use a "Create role" statement to create the role:

create role roledeep

Output



Now we provide the alter permission to role roledeep.

grant alter to roledeep

Output



Now we provide this permission to the user user24. 

grant roledeep to user24

Output



Now we add a new user User25

alter role roledeep
add member user25

Output



Now we remove user user24

alter role roledeep
drop member user24

Output



Revoking the alter permission from the role roledeep

revoke alter to roledeep

Output



Droping the role roleDeep 

drop role roledeep

Output



Altering the role

We change the name of role roledeep to roledeepak.

alter role roledeep
with name=roledeepak

Output



Summary

In this article I described Role in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.