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:
  1. create table emp(empId int,empName varchar(15))
Insertion Of Data
Use the following command to insert data in the table:

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

Output

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

  1. select * from emp
role-in-sql-server.jpg

Creation of role

We use a "Create role" statement to create the role:
  1. create role roledeep
Output

role-in-sql-serverr.jpg

Now we provide the alter permission to role roledeep.
  1. grant alter to roledeep
Output

role-in-sql-serverr.jpg

Now we provide this permission to the user user24.

  1. grant roledeep to user24
Output

role-in-sql-serverr.jpg

Now we add a new user User25
  1. alter role roledeep
  2. add member user25
Output

role-in-sql-serverr.jpg

Now we remove user user24
  1. alter role roledeep
  2. drop member user24
Output

role-in-sql-serverr.jpg

Revoking the alter permission from the role roledeep

  1. revoke alter to roledeep
Output

role-in-sql-serverr.jpg

Droping the role roleDeep

  1. drop role roledeep
Output

role-in-sql-serverr.jpg

Altering the role

We change the name of role roledeep to roledeepak.
  1. alter role roledeep
  2. with name=roledeepak
Output

role-in-sql-serverr.jpg

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.