How to create user-defined role in SQL Server Database?

Question

Ashutosh Kumar Verma · Answer

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;
and 
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 and database. The emp_report is the role name.
and 
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;
and 
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
and 
and 
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.
and 
Also, Read: Describe the different types roles in SQL Server.

forum

How to create user-defined role in SQL Server Database?

Sandra Emily

1 Answers

SQL Server User-Defined Role

Liked By