SQL Server User-Defined Role

The following example creates a new user and role, grants permissions to the role, and adds a user to the role.

First, set the current database to master and create a new login called krishna

USE master
GO
CREATE LOGIN krishna 
WITH PASSWORD='Radhe.K$%108#';

Second, switch the current database to MyCollegeDb and create a new user called krishna for login,

USE MyCollegeDb
GO
CREATE USER krishna 
FOR LOGIN krishna;

Create Role

Create a new role called emp_report in the MyCollegeDb database

USE MyCollegeDb
GO
CREATE ROLE emp_report;

In this example, we use the CREATE ROLE statement to create a new role in the MyCollegeDb  database. The emp_report is the role name.

Grant Permission to the Role

Grant the SELECT privilege on the dbo schema to the emp_report

USE MyCollegeDb
GO
GRANT SELECT 
ON SCHEMA :: dbo
TO emp_report;

Add User to the Role

Add the krishna user to the emp_report role

USE MyCollegeDb
GO
ALTER ROLE emp_report
ADD MEMBER krishna;

Finally, connect to the MyCollegeDb database using user krishna. In this case, user krishna can only view tables in the dbo schema. Also, user krishna can only select data from tables in this dbo schema because the user is a member of emp_report which has the SELECT privilege

Let's try to update the records in the above table using the SQL query to verify the granted permission,

In the above case the SQL Server denied permission to update the data.

Also, Read: Describe the different types roles in SQL Server.