Describe the different type of roles in SQL Server.

Question

Ashutosh Kumar Verma · 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.and Third, add one or more users to the role.
and 
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.
and 
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.
and 
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 and 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,
LOGINDATABASEOBJECTROLESCHEMAUSER
Finally, switch the connection to the user MyUserand select data from the
dbo.Employeestable,
and 
Also, Read: 
How to create user-defined role in SQL Server Database?

forum

Describe the different type of roles in SQL Server.

Sandra Emily

Can you answer this question?

1 Answers

SQL Server Roles

SQL Server Role Type

Adding a user to a Role

Liked By