---
title: "Describe the different type of roles in SQL Server."  
description: "Describe the different type of roles in SQL Server."  
author: "Sandra Emily"  
published: 2024-07-15  
updated: 2024-07-16  
canonical: https://www.mindstick.com/forum/160905/describe-the-different-type-of-roles-in-sql-server  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 2 minutes  

---

# Describe the different type of roles in SQL Server.

[Describe](https://www.mindstick.com/interview/12752/what-is-ddms-describe-some-of-its-capabilities) the different type of [roles](https://www.mindstick.com/articles/1366/membership-roles-user-profile-in-asp-dot-net) in SQL Server.

## Replies

### Reply by Ashutosh Patel

#### SQL Server Roles

A role is a set of permissions. Functions help simplify route planning. For example, instead of assigning permissions to individual users, you can group permissions into roles and add users to those roles.

- First, create a role.
- Second, grant permissions to the role.
- Third, add one or more users to the role.

####

#### SQL Server Role Type

SQL Server provides you with three main role types,

**Server-level Roles** – manage the permissions on SQL Server, like changing server configuration.\
**Database Level Roles** – Manage permissions on databases such as creating tables and querying data.\
**Application level Role**– Allow applications to run with their own, user-like permissions.\

SQL Server provides two types for each role,

**Fixed server roles** are created roles provided by SQL Server. These functions have fixed licenses.\
**User-defined roles** creates a role that you define to meet a specific security need.

#### Adding a user to a Role

First, create a new login called `testLogin`

```plaintext
CREATE LOGIN testLogin
WITH PASSWORD='UyxIv@12'
```

Now, switch the current database to `MyCollegeDb` and create a user for `testLogin`login

```plaintext
USE MyCollegeDb
GO

CREATE USER MyUser
FOR LOGIN testLogin;
```

Then, connect to the `MyCollegeDb` database using user `MyUser` . User `MyUser` can view the `MyCollegeDb` database but cannot see any database objects.

After that, add the user `MyUser` to the `db_datareader` role.

```plaintext
Use MyCollegeDb
GO
ALTER ROLE db_datareader
ADD MEMBER MyUser
```

`db_datareader` is a fixed database role. The `db_datareader` role allows all members to read data from all user **tables** and **views** in the database. Technically, it is equivalent to the following `GRANT` statement

```plaintext
GRANT SELECT
ON DATABASE::MyCollegeDb
TO MyUser;
```

In this example, `DATABASE` is a class type that comes after `::` indicating a separable that is a database. Following are the available class types,

- LOGIN
- DATABASE
- OBJECT
- ROLE
- SCHEMA
- USER

Finally, switch the connection to the user `MyUser`and select data from the `dbo.Employees`table,

![Describe the different type of roles in SQL Server.](https://www.mindstick.com/mindstickforums/05446195-1af2-408b-9cee-b1f0734e7372/images/79b6cd78-473c-4b05-808e-94d904856cf9.png)

**Also, Read:** [How to create user-defined role in SQL Server Database?](https://www.mindstick.com/forum/160923/how-to-create-user-defined-role-in-sql-server-database)


---

Original Source: https://www.mindstick.com/forum/160905/describe-the-different-type-of-roles-in-sql-server

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