Reader Level:
ARTICLE

Role in SQL Server

Posted by Deepak Arora Articles | SQL Server November 21, 2012
In this article I described Role in SQL Server.
  • 0
  • 0
  • 5412

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

role-in-sql-server.jpg

Creation of role

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


create
role roledeep

Output:

role-in-sql-serverr.jpg

Now we provide the alter permission to role roledeep.

grant alter to roledeep

Output:

role-in-sql-serverr.jpg

Now we provide this permission to the user user24:

grant roledeep to user24

Output:

role-in-sql-serverr.jpg

Now we add a new user User25 :


alter
role roledeep
add
member user25

Output:

role-in-sql-serverr.jpg

Now we remove user user24:


alter
role roledeep
drop
member user24

Output:

role-in-sql-serverr.jpg

Revoking the alter permission from the role roledeep:

revoke alter to roledeep

Output:

role-in-sql-serverr.jpg

Droping the role roleDeep:

drop role roledeep

Output:

role-in-sql-serverr.jpg

Altering the role:

We change the name of role roledeep to roledeepak.


alter
role roledeep
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.

COMMENT USING

Trending up