Blog Post

Azure SQL Blog
5 MIN READ

New server roles for Azure SQL Database and SQL Server 2022 in Public Preview

AndreasWolter's avatar
AndreasWolter
Icon for Microsoft rankMicrosoft
May 25, 2022

New server roles for Azure SQL Database and SQL Server 2022 in Public Preview: Database Management without admin-access

 

We are now releasing a new set of server roles which support server- and database management without using admin-accounts and in addition make administration at scale even easier to Public Preview.

The 4 additional roles are:

  1. ##MS_DatabaseConnector##
  2. ##MS_SecurityDefinitionReader##
  3. ##MS_DatabaseManager##
  4. ##MS_LoginManager##


- September 2021 we announced the first set of new server roles for Azure SQL Database: Server roles for Azure SQL Database: Database Management without admin-access

the previously released roles are:

  1. ##MS_DefinitionReader##
  2. ##MS_ServerStateReader##
  3. ##MS_ServerStateManager##

 

Benefit

The new server-roles that can be assigned to server logins to enable customers to assign and delegate job functions for server-wide metadata access and access to certain management commands without requiring Server Admin or AAD Admin privileges. This helps comply with the Principle of Least Privilege and implement role separation (sometimes also interchangeably referred to as Separation of Duties).

 

Technical background

Each of these new server-roles includes a specific set of permissions. In SQL Database those permissions cannot be assigned individually on the server scope but using those roles one can grant them to a Login. This is a new method to grant server permissions across a logical server and all databases hosted on that logical server in Azure SQL Database, aside from assigning Server/AAD Admin, which in many cases is considered too powerful.

In the following let’s have a look at each role individually:

 

##MS_SecurityDefinitionReader##

The ##MS_SecurityDefinitionReader##-role predominantly will be useful for read-only server-auditing where an auditor will need to see the contents of system-related static system metadata such as Login and User names, permissions of principals and role membership.

It has VIEW ANY SECURITY DEFINITION permission on the server, which covers and hence includes the VIEW SECURITY DEFINITION permission on any database that a principal in that role has access to.

 

##MS_DatabaseConnector##

This role is likely the most important new role as it allows members to connect to any database without requiring a User-account in the respective databases to connect to. In combination with the other new server roles, this essentially leads to the server level role-based permissions to inherit to the respective database scoped permissions.

This is because it holds the permission - holds CONNECT ANY DATABASE on server-scope, which then inherits the CONNECT permission on each database.

i.e., a member of ##MS_DatabaseConnector## + ##MS_ServerStateReader## will have the permission VIEW SERVER STATE + VIEW DATABASE STATE on all databases, without requiring Users to be created. This greatly simplifies permission assignment via roles at scale.

Tipp: To exclude specific databases on a given server from this “grant access to all”-concept, you can proceed as follows: create matching User-accounts for the given login in those databases and then DENY the CONNECT permission to the database-user. This DENY permission will overrule the GRANT CONNECT permission coming from this role.

 

The ability to grant the Connect-permission to all databases (without further permissions) makes this role the ideal counterpart for ##MS_ServerStateReader##, ##MS_DefinitionReader## and ##MS_SecurityDefinitionReader## server roles.
The following diagram shows how a Login “Juanita” is member of both the ##MS_DatabaseConnector## (green in diagram) and the ##MS_ServerStateReader## (blue) roles and in result can connect to any database and read any Server and database level DMV (Dynamic Management View).

server roles in combination

 

The diagram shows that in addition to the Server level permissions (CONNECT ANY DATABASE and VIEW SERVER STATE), Juanita is also granted the respective inherited database-permissions (CONNECT and VIEW DATABASE STATE).

 

##MS_DatabaseManager##

Members of the ##MS_DatabaseManager## fixed server role can create and delete databases. A member of the ##MS_DatabaseManager## role that creates a database, becomes the owner of that database, which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database. Members of the ##MS_DatabaseManager## role don't necessarily have permission to access databases that they don't own.
For Azure SQL Database, it is recommended to use this server role instead of the special dbmanager database level role that exists in master (Azure SQL DB only) and was previously used to reach the same goals but required a User account in master-database.

 

##MS_LoginManager##

Members of the ##MS_LoginManager## fixed server role can create and delete logins.
Just as with the ##MS_DatabaseManager##-role, for Azure SQL Database, it is recommended to use this server role over the special loginmanager database level role that exists in master and was previously used to reach the same goals but required a User account in master-database.

 

Summary

The new server roles in conjunction with the previous set enable customers to grant specific lower privileges to personnel that should not have full admin-permissions and by using the ##MS_DatabaseConnector##-role can do so in a scalable manner: for a whole logical server in Azure SQL Database.
When it comes to SQL Server 2022 (now in Public Preview), these roles could be imitated by creating custom server roles which are available in SQL Server, but including them as built-in roles the same way as for Azure SQL DB will hopefully enable simpler management from cloud to on-prem and for migration back & forth. Also we are aware that some customers often prefer to use built-in roles over custom roles per principle.

 

Limitations

  • Role assignments may take up to 5 minutes to become effective. In addition, for existing sessions, changes to server role assignments do not take effect until the connection is closed and re-opened. This is due to distributed architecture between the master database and other databases on the same logical server.
    Partial workaround: to reduce the up to 5-minute waiting period and ensure that server role assignments are current in a database, a Server Admin/AAD Admin can run DBCC FLUSHAUTHCACHE in the user database(s) on which the login has access. Currently logged on users still need to reconnect after this for the membership-changes to take effect on them.
  • These roles can be assigned to SQL Logins and AAD Logins. However, currently Group-based AAD Logins are not yet supported. This is functionality will be delivered later.

 

Further references:

 

 

Updated May 25, 2022
Version 3.0
azure sql security
  • AFomchenko's avatar
    AFomchenko
    Copper Contributor
    Sep 16, 2024

    Hi everyone,

    I am facing an strange issue dealing with those server roles in my Azure DB environment when using a login derived from AAD Group (Microsoft Entra) for low-privileged activities (such as create DBs, deploy changes in it).
    It does work when I am using a simple SQL Login but does not work with AAD Groups.

    For example if I add AAD Login (Group) to ##MS_DatabaseConnector##, I am not able to login to a virtual server and getting the following error: Login failed for user '<token-identified principal>'. (Framework Microsoft SqlClient Data Provider)


    Script is very trivial:

    CREATE LOGIN [SG-User] FROM  EXTERNAL PROVIDER;
    GO
    ALTER SERVER ROLE ##MS_DatabaseConnector## ADD MEMBER [SG-User];
    GO

    I know it is possible to use another way to grant access via users in master DB and adding to specific master roles but I would like to make it work according to Microsoft recommendation where those new Server Roles is the right way to go (for ).

    Can someone please point me to the right direction if this is a know issue?
    So far I have not been able to locate similar issues in the web.

    Thank you
  • Ronen_Ariely's avatar
    Ronen_Ariely
    MVP
    May 30, 2022

     

     

     Database Management without (sys)admin-access fits the same analogy since many times we need users to manage the database but not the have full permission in the server level for the server itself

     

    Note: Above slides are from My PowerPoint from 2016 lecture about SQL Server Always Encrypted

  • Ronen_Ariely's avatar
    Ronen_Ariely
    MVP
    May 30, 2022

    Obviously :smile:

    In fat, the basic idea of Database Management without (sys)admin-access makes a lot of sense. It related to one of the analogy I do when I speak about SQL Server Always Encrypted. Let me find the slide and share the analogy which I do between databases and parking lot :lol:

  • AndreasWolter's avatar
    AndreasWolter
    Icon for Microsoft rankMicrosoft
    May 30, 2022

    Sure thing, Ronen_Ariely .

    I am certain these roles are very useful for security-minded folks, and I want to make sure as many folks as possible learn about them and start using them and by that lower admin-account usage.