Describe the different type of roles in SQL Server.

Question

Ashutosh Patel · Answer

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

CREATE LOGIN testLogin
WITH PASSWORD='UyxIv@12'

Now, switch the current database to MyCollegeDb and create a user for testLoginlogin

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.

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

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 MyUserand select data from the dbo.Employeestable,

Also, Read: How to create user-defined role in SQL Server Database?

forum

Describe the different type of roles in SQL Server.

Can you answer this question?

1 Answers

SQL Server Roles

SQL Server Role Type

Adding a user to a Role