Fixed Server Roles in Azure SQL Database Server

This article is for discussing fixed server roles in Azure SQL Database Server.

Introduction

Azure SQL Database provides relational Microsoft SQL Server in the PAAS (platform-as-a-service) offering. The database functionality is similar to the on-premises SQL database (with limited features), but the administrators cannot control the database configurations entirely. In the on-premises environment, we do have SQL instances and underlying databases existing on that instance. You can control user permissions from the instance, database level.

For example, you have several roles in on-premises instances such as Sysadmin, Serveradmin, Setupadmin, Securityadmin.

Once we deploy an Azure SQL DB, it requires a logical Azure SQL Server. The logical server does not allow database administrators to manage permissions similar to the on-premises SQL Server. Users cannot do instance-level configurations for the logical server. You do not get access to security roles in the Azure SQL Server. Try connecting to Azure DB in SSMS, and it does not display the server roles in the security tab at the instance level.

If you connect to the master database in SSMS, the security shows the existing login accounts.

However, connecting directly to your specific Azure SQL DB displays the security controls inside a database.

You can manage the security, logins at the database level. However, for simplifying the permissions, Azure provides a few fixed server-level roles. These server-level roles help DBA to manage the user’s permissions on the logical server.

Requirements

To follow along with this article, you should deploy an Azure SQL Database. You also require the SQL Server admin user to connect to the master or azure database and execute specific queries. If you are not aware of Azure SQL, refer to the SQL Azure category.

Let’s explore the fixed server roles in Azure SQL Database.

Built-in server-level roles Azure SQL Server

Azure Server built-in server role has prefix ##MS and suffix ## so that users can be distinguished from these roles. These built-in server roles help you manage several logins, their permissions without providing server admin or AD admin privileges. It helps administrators to comply with the Principle of Least Privilege using role separation.

The following table lists these roles and their description.

##MS_DefinitionReader##
The role allows users to read catalog views covered by the VIEW ANY DEFINITION, VIEW DEFINITION for Azure database in which the user exists.
- Server-level permissions: VIEW ANY DATABASE, VIEW ANY DEFINITION, VIEW ANY SECURITY DEFINITION
- Database-level permissions: VIEW DEFINITION, VIEW SECURITY DEFINITION

##MS_ServerStateReader##
In this fixed server role, the user can execute all dynamic management views (DMV) and functions covered by the VIEW SERVER STATE and VIEW DATABASE STATE on the Azure database in which the role contains a user account.
- Server-level permissions: VIEW SERVER STATE, VIEW SERVER PERFORMANCE STATE, VIEW SERVER SECURITY STATE
- Database-level permissions: VIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE

##MS_ServerStateManager##
The ##MS_ServerStateManager## role contains the permissions of ##MS_ServerStateReader## and an additional permission for ALTER SERVER STATE. It allows users to execute DBCC commands – ), DBCC SQLPERF(),DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE (‘ALL’), DBCC SQLPERF();
- Server-level permissions: ALTER SERVER STATE, VIEW SERVER STATE, VIEW SERVER PERFORMANCE STATE, VIEW SERVER SECURITY STATE
- Database-level permissions: VIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE

Note: Users (admins) cannot modify the permissions assigned to the fixed server roles.

The permissions granted to the fixed server roles cannot be changed, and these roles can’t have other fixed roles as members. The Azure server roles work similarly to the roles in the on-prem SQL instance. The user server role permissions can propagate to database permissions. However, the login must exist in the respective Azure SQL database.

Let’s say, a user [Demo1] has server level role ##MS_ServerStateReader##. The role ##MS_ServerStateReader## contains permission VIEW SERVER STATE. The user [Demo1] has a login in the Azure DB [azuresqldemo]. Therefore, the user [Demo1] will have database level permission -VIEW DATABASE STATE permission in both master and [azuresqldemo] database.

Note: You cannot work with the server-level roles in SSMS GUI. You can create, edit, or remove users from these groups using T-SQL scripts.

The following section creates a new SQL login user and provides permissions through the defined fixed server roles. It will give you a better understanding of how this built-in role works.

Create Azure SQL Database Logins and Users

To create a new SQL Database login in Azure SQL Database, connect to Azure SQL Server using administrator user credentials. The below script creates a new login [DemoLogin1] and user in the master database.

CREATE LOGIN DemoLogin1 WITH PASSWORD = 'P@ssw0rd!23';

CREATE USER DemoLogin1 FROM LOGIN DemoLogin1

Now, add the user as a member of the ##MS_ServerStateReader## role using the following ALTER SERVER ROLE.

ALTER SERVER ROLE ##MS_ServerStateReader##

ADD MEMBER DemoLogin1;

We can use the sys.server_role_members and sys.sql_logins catalog views to return SQL logins having memberships to any fixed server role.

SELECT

sql_logins.name AS MemberPrincipalName

,roles.name AS RolePrincipalName

FROM sys.server_role_members AS server_role_members

INNER JOIN sys.server_principals AS roles

ON server_role_members.role_principal_id = roles.principal_id

INNER JOIN sys.sql_logins AS sql_logins

ON server_role_members.member_principal_id = sql_logins.principal_id;

As shown below, the login [DemoLogin1] is part of the built-in server role ##MS_ServerStateReader##.

The VIEW SERVER STATE allows users to execute Server-scoped dynamic management views and functions such as sys.dm_os_wait_stats.

Connect to the master database using SSMS and run the following query. You get the expected results.

SELECT * FROM sys.dm_os_wait_stats

We have not added this user to the Azure SQL Database [azuredemodatabase]. Therefore, if you try to access the database, it gives the following error.

To add the login as an Azure SQL Database user, run the following CREATE USER statement under the admin security context.

CREATE USER DemoLogin1 FROM LOGIN DemoLogin1

The user automatically gets the permissions assigned to the server level role ##MS_ServerStateReader##. Therefore, it can execute the query that requires VIEW DATABASE STATE permissions. For example, you can use database scoped database management view sys.dm_db_log_info to return the number of virtual log files in a database as below.

SELECT [name], COUNT(l.database_id) AS 'vlf_count'

FROM sys.databases s

CROSS APPLY sys.dm_db_log_info(s.database_id) l

GROUP BY [name]

HAVING COUNT(l.database_id) > 1

Queries to check role memberships

IS_SRVROLEMEMBER() function to check user access

Azure SQL Database supports the function IS_SRVROLEMEMBER() to check whether the SQL Server login is a member of the specified server role.

To check the server-role membership for the role ##MS_ServerStateReader## of logged-in User.
- Value 1: Yes, the user is a member of a specified role
- Value 0: No, the user is not a member of the specified role

SELECT IS_SRVROLEMEMBER('##MS_ServerStateReader##') AS LoginIsAMemberofGroup

Note: The master database in Azure SQL Server does not support the function IS_SRVROLEMEMBER().

sys.fn_my_permissions()

To return the fixed server role permissions at the server or database level, you can use the function – sys.fn_my_permissions().

For example, in my demo, the user [demologin] is a member of the fixed server role ##MS_ServerStateReader##. Therefore, the query returns the following output.

SELECT * FROM sys.fn_my_permissions(NULL, 'Server')

SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE')

Limitations of server-level roles

Azure might take up to 5 minutes for role assignment to be effective.
If you are already connected to the Azure database, you need to reopen the connection to distribute server or database role permissions for users.
To reduce the waiting period (up to 5 minutes), the Azure SQL administrator can run the command – DBCC FLUSHAUTHCACHE. It empties the SQL database authentication cache that contains information about logins, firewall rules for the current user database. However, you still need to reconnect Azure DB. You can refer to Microsoft docs for more details.

Note: You cannot execute DBCC FLUSHAUTHCACHE on the master database because the master database has information (physical storage) for the logins, firewall rules.

Conclusion

This article explored built-in server roles for the logical Azure SQL Server. The Database administrators can manage the permissions for the users using these roles. Once we add a login/user in the required role, it automatically assigns the server and database level permissions. However, you should create the database user in Azure SQL Database to access the database with assigned permissions.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023