---
title: "How to create user-defined role in SQL Server Database?"  
description: "How to create user-defined role in SQL Server Database?"  
author: "Sandra Emily"  
published: 2024-07-16  
updated: 2024-07-16  
canonical: https://www.mindstick.com/forum/160923/how-to-create-user-defined-role-in-sql-server-database  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 2 minutes  

---

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

How to create [user](https://www.mindstick.com/articles/13001/multi-statement-table-valued-user-defined-function-in-sql-server)-defined [role](https://yourviews.mindstick.com/audio/1254/the-role-of-visualization-in-achieving-your-goals) in SQL [Server Database](https://www.mindstick.com/forum/156824/what-is-normalization-in-sql-server-database)?

## Replies

### Reply by Ashutosh Patel

#### 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](https://www.mindstick.com/articles/12226/use-of-database-in-sencha-extjs-and-insert-record-from-user-form-using-ajax) to `master` and create a new login called `krishna`

```plaintext
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,

```plaintext
USE MyCollegeDb
GO
CREATE USER krishna
FOR LOGIN krishna;
```

####

## Create Role

Create a new role called `emp_report` in the `MyCollegeDb` database

```plaintext
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`

```plaintext
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

```plaintext
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

![How to create user-defined role in SQL Server Database?](https://www.mindstick.com/mindstickforums/64b6bde4-94be-4450-acd2-6313584a18e0/images/96ea072d-d0ed-4eea-8bb2-8accc00d074f.png)

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

![How to create user-defined role in SQL Server Database?](https://www.mindstick.com/mindstickforums/64b6bde4-94be-4450-acd2-6313584a18e0/images/3d288293-b06e-4862-a387-9f46fd05ee59.png)

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

**Also, Read:** [Describe the different types roles in SQL Server.](https://www.mindstick.com/forum/160905/describe-the-different-types-roles-in-sql-server)


---

Original Source: https://www.mindstick.com/forum/160923/how-to-create-user-defined-role-in-sql-server-database

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.