Rajendra Gupta
Memberships

Fixed Server Roles in Azure SQL Database Server

April 6, 2022 by

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.

On-Prem SQL Server

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.

Security logins

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

Azure SQL Database controls

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.

  1. ##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
  1. ##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
  1. ##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.

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

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.

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

View built-in server roles

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.

Connect using SSMS

OS waits

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.

Access error

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

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.

Log status

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

check role memberships

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.

Memberships

Database permissions

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.

Rajendra Gupta
2,039 Views