How to List or Modify User Roles in SQL Server

3.4K

Microsoft SQL Server roles are a central part of database server security. Roles are used to control access to objects and assign permissions to users and/or security groups. SQL Server roles are similar to Windows user groups. In this article we will look at how to view the list of SQL Server roles assigned to a user and how to add/remove a role.

SQL Server predefined roles

SQL Server includes several predefined (fixed) server and database roles. SQL Server 2022 also introduces additional fixed server roles that provide more granular permissions based on the Principle of Least Privilege:

• Fixed server-level roles – built-in roles used to assign permission on SQL Server. For example, these roles allow users to change server config, create databases, and perform other admin tasks.
• Fixed database-level roles – allow to manage permissions on databases. These roles are commonly used when granting permissions to create tables/query data.

Permissions assigned to fixed SQL Server roles cannot be changed.

The administrator can also create user-defined server or database roles with custom set of permissions.

Managing User Roles with Microsoft SQL Server Management Studio

Use Microsoft SQL Server Management Studio (SSMS) to view a list of fixed SQL Server roles. Run the SSMS and connect to the database engine.

Fixed server roles

Fixed server roles are available under Security > Server roles. The following fixed server roles are available in SQL Server 2019 and earlier:

• sysadmin
• serveradmin
• securityadmin
• processadmin
• setupadmin
• bulkadmin
• diskadmin
• dbcreator
• public

SQL Server 2022 and later

Beginning with SQL Server 2022, Microsoft introduced additional fixed server roles based on the Principle of Least Privilege. These roles provide more granular admin permissions and we recommend using them instead of broader legacy roles whenever possible.

The most commonly used SQL Server 2022 fixed server roles include:

Role	Purpose
##MS_DatabaseConnector##	Connect to databases without requiring a user account in each database.
##MS_LoginManager##	Create, alter, and manage SQL Server logins.
##MS_DatabaseManager##	Create, alter, and drop databases.
##MS_ServerStateManager##	View and manage server state-related settings and operations.
##MS_ServerStateReader##	View server state information such as DMVs and monitoring data.
##MS_ServerPerformanceStateReader##	View performance-related server state information and diagnostics.
##MS_ServerSecurityStateReader##	View security-related server state and security metadata.
##MS_DefinitionReader##	View metadata definitions for server and database objects.
##MS_PerformanceDefinitionReader##	View performance-related object definitions and metadata.
##MS_SecurityDefinitionReader##	View security-related object definitions, permissions, and metadata.

Note. The classic fixed server roles remain available for backward compatibility. However, SQL Server 2022 introduces the ##MS_*## roles to support the Principle of Least Privilege by granting more narrowly scoped admin permissions.

Database roles

Each database has its own set of fixed DB-level roles. Expand the Database > Security > Roles > Database Roles:

• db_owner
• db_securityadmin
• db_accessadmin
• db_backupoperator
• db_ddladmin
• db_datawriter
• db_datareader
• db_denydatawriter
• db_denydatareader
• public

Viewing User Role Membership in SSMS

You can view the roles assigned to a specific user/login in SQL Server Management Studio. In order to view server-level roles, follow the steps below:

1. Expand Security > Logins.
2. Right-click the login and select Properties.
3. Open the Server Roles tab.
4. Review the server roles assigned to the login.

To view database-level roles:

1. Expand Databases > DatabaseName > Security > Users.
2. Right-click the user and select Properties.
3. Open the Membership page.
4. Review the database roles assigned to the user.

This method provides a quick way to verify role assignments without using Transact-SQL queries.

These are the standard fixed roles available in most user databases. Some system databases (such as msdb) include additional predefined roles for SQL Agent and maintenance operations.

Members of a database role can be:

• Windows and/or Active Directory groups and user accounts
• SQL Server logins
• Other roles (don’t add user-defined database roles as members of fixed roles; this could be used for privilege escalation)

Example: assigning fixed SQL Server roles to AD user account

In this example, we are going to assign several fixed SQL Server roles to an Active Directory user account.

1. First, add the AD user to SQL Server Logins. Expand Security > Logins and select New Login;
   

   

2. Select Windows authentication and click the Search button;
3. Select your domain as the search location, type the AD user name and click Check Names;

   

4. Next, go to Server Roles and select the global server roles that you want to grant to the user.
   

   

5. The new AD user account should appear in the SQL server Logins list.
   

   

6. If you want to add or remove user roles, open the account properties and make changes on the Server Roles tab.

If you want to grant a DB-level role to an AD user, first add their account to the list of DB users, then open the database role properties and add a user as a role member.

Note that you should grant only the minimum permissions required. You need to avoid assigning sysadmin unless absolutely necessary. Also, keep in mind that members of the sysadmin role bypass all permission checks and have unrestricted access to every SQL Server resource.

How to Add or List User Roles in SQL Server with Transact-SQL Queries

You can use the T-SQL language queries to add or remove SQL Server roles from users.

Open the SSMS and click New Query.

You can use the following query to display all fixed and user-defined server roles available on the SQL Server instance:

SELECT name
FROM sys.server_principals
WHERE type = 'R'
ORDER BY name;

You can use the query below to list all database roles in the currently selected database:

SELECT name
FROM sys.database_principals
WHERE type = 'R'
ORDER BY name;

In order to add an AD user to SQL server logins, use the following query:

CREATE LOGIN [THEITBROS\k.ziegler]
FROM WINDOWS;
GO

Paste this code into the SSMS query window and the click the Execute button.

Now you can assign a server role to user:

ALTER SERVER ROLE serveradmin
ADD MEMBER [THEITBROS\k.ziegler];

Assign a db-level role:

USE DBNAME;
GO

CREATE USER [THEITBROS\k.ziegler]
FOR LOGIN [THEITBROS\k.ziegler];

ALTER ROLE db_backupoperator
ADD MEMBER [THEITBROS\k.ziegler];

To remove a user from the server role, run:

ALTER SERVER ROLE serveradmin
DROP MEMBER [THEITBROS\k.ziegler];

Or from a DB role:

ALTER ROLE db_backupoperator
DROP MEMBER [THEITBROS\k.ziegler];

List server roles a user is member of:

SELECT r.name as Role, m.name as Principal

from

master.sys.server_role_members rm

inner join

master.sys.server_principals r on r.principal_id = rm.role_principal_id and r.type = 'R'

inner join

master.sys.server_principals m on m.principal_id = rm.member_principal_id

WHERE m.name = 'THEITBROS\k.ziegler'

How to Check Current Role Membership

To quickly check if the current login belongs to a specific server/database role, you can use the built-in role membership functions.

In order to check membership in a server role, use the following query:

SELECT IS_SRVROLEMEMBER('sysadmin');

To check membership in a database role:

SELECT IS_ROLEMEMBER('db_owner');

A return value of 1 indicates that the current user is a member of the role, 0 indicates that they are not, and NULL means the role or principal could not be validated.

Query assigned user roles in a specific database:

use DBNAME;
select P.Name, R.Name
from sys.database_principals P
left outer join sys.database_role_members RM on P.principal_id=RM.member_principal_id
left outer join sys.database_principals R on R.principal_id=RM.role_principal_id
where P.Name = 'THEITBROS\k.ziegler'
ORDER BY R.Name;