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.
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:
Creation of role
We use a "Create role" statement to create the role:
Output
Now we provide the alter permission to role roledeep.
Output
Now we provide this permission to the user 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
Output
Droping the 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.