Azure SQL Database server roles for permission management
Applies to: Azure SQL Database
This article describes fixed server-level roles in Azure SQL Database.
Note
The fixed server-level roles in this article are in public preview for Azure SQL Database. These server-level roles are also part of the release for SQL Server 2022.
Overview
In Azure SQL Database, the server is a logical concept and permissions can't be granted at the server level. To simplify permission management, Azure SQL Database provides a set of fixed server-level roles to help you manage the permissions on a logical server. Roles are security principals that group logins.
Note
The roles concept in this article are like groups in the Windows operating system.
These special fixed server-level roles use the prefix ##MS_
and the suffix ##
to distinguish from other regular user-created principals.
Like SQL Server on-premises, server permissions are organized hierarchically. The permissions that are held by these server-level roles can propagate to database permissions. For the permissions to be effectively useful at the database level, a login needs to either be a member of the server-level role ##MS_DatabaseConnector##
, which grants CONNECT
to all databases, or have a user account in individual databases. This also applies to the virtual master
database.
For example, the server-level role ##MS_ServerStateReader##
holds the permission VIEW SERVER STATE
. If a login who is member of this role has a user account in the databases master
and WideWorldImporters
, this user has the permission VIEW DATABASE STATE
in those two databases.
Note
Any permission can be denied within user databases, in effect, overriding the server-wide grant via role membership. However, in the system database master, permissions can't be granted or denied.
Azure SQL Database currently provides seven fixed server roles. The permissions that are granted to the fixed server roles can't be changed and these roles can't have other fixed roles as members. You can add server-level logins as members to server-level roles.
Important
Each member of a fixed server role can add other logins to that same role.
For more information on Azure SQL Database logins and users, see Authorize database access to SQL Database, SQL Managed Instance, and Azure Synapse Analytics.
Fixed server-level roles
The following table shows the fixed server-level roles and their capabilities.
Fixed server-level role | Description |
---|---|
##MS_DatabaseConnector## |
Members of the ##MS_DatabaseConnector## fixed server role can connect to any database without requiring a User-account in the database to connect to.To deny the CONNECT permission to a specific database, users can create a matching user account for this login in the database and then DENY the CONNECT permission to the database-user. This DENY permission overrules the GRANT CONNECT permission coming from this role. |
##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. You should use this server role over the dbmanager database level role that exists in master . |
##MS_DefinitionReader## |
Members of the ##MS_DefinitionReader## fixed server role can read all catalog views that are covered by VIEW ANY DEFINITION , respectively VIEW DEFINITION on any database on which the member of this role has a user account. |
##MS_LoginManager## |
Members of the ##MS_LoginManager## fixed server role can create and delete logins. You should use this server role over the loginmanager database level role that exists in master . |
##MS_SecurityDefinitionReader## |
Members of the ##MS_SecurityDefinitionReader## fixed server role can read all catalog views that are covered by VIEW ANY SECURITY DEFINITION , and respectively has VIEW SECURITY DEFINITION permission on any database on which the member of this role has a user account. This is a small subset of what the ##MS_DefinitionReader## server role has access to. |
##MS_ServerStateManager## |
Members of the ##MS_ServerStateManager## fixed server role have the same permissions as the ##MS_ServerStateReader## role. Also, it holds the ALTER SERVER STATE permission, which allows access to several management operations, such as: DBCC FREEPROCCACHE , DBCC FREESYSTEMCACHE ('ALL') , DBCC SQLPERF() ; |
##MS_ServerStateReader## |
Members of the ##MS_ServerStateReader## fixed server role can read all dynamic management views (DMVs) and functions that are covered by VIEW SERVER STATE , respectively VIEW DATABASE STATE on any database on which the member of this role has a user account. |
Permissions of fixed server roles
Each fixed server-level role has certain permissions assigned to it. The following table shows the permissions assigned to the server-level roles. It also shows the database-level permissions, which are inherited as long as the user can connect to individual databases.
Fixed server-level role | Server-level permissions | Database-level permissions (if a database user matching the login exists) |
---|---|---|
##MS_DatabaseConnector## |
CONNECT ANY DATABASE |
CONNECT |
##MS_DatabaseManager## |
CREATE ANY DATABASE , ALTER ANY DATABASE |
ALTER |
##MS_DefinitionReader## |
VIEW ANY DATABASE , VIEW ANY DEFINITION , VIEW ANY SECURITY DEFINITION |
VIEW DEFINITION , VIEW SECURITY DEFINITION |
##MS_LoginManager## |
CREATE LOGIN , ALTER ANY LOGIN |
N/A |
##MS_SecurityDefinitionReader## |
VIEW ANY SECURITY DEFINITION |
VIEW SECURITY DEFINITION |
##MS_ServerStateManager## |
ALTER SERVER STATE , VIEW SERVER STATE , VIEW SERVER PERFORMANCE STATE , VIEW SERVER SECURITY STATE |
VIEW DATABASE STATE , VIEW DATABASE PERFORMANCE STATE , VIEW DATABASE SECURITY STATE |
##MS_ServerStateReader## |
VIEW SERVER STATE , VIEW SERVER PERFORMANCE STATE , VIEW SERVER SECURITY STATE |
VIEW DATABASE STATE , VIEW DATABASE PERFORMANCE STATE , VIEW DATABASE SECURITY STATE |
Permissions
Only the server admin account or the Microsoft Entra admin account (which can be a Microsoft Entra group) can add or remove other logins to or from server roles. This is specific to Azure SQL Database.
Note
Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).
Work with server-level roles
The following table explains the system views, and functions that you can use to work with server-level roles in Azure SQL Database.
Feature | Type | Description |
---|---|---|
IS_SRVROLEMEMBER | Metadata | Indicates whether a SQL login is a member of the specified server-level role. |
sys.server_role_members | Metadata | Returns one row for each member of each server-level role. |
sys.sql_logins | Metadata | Returns one row for each SQL login. |
ALTER SERVER ROLE | Command | Changes the membership of a server role. |
Examples
The examples in this section show how to work with server-level roles in Azure SQL Database.
A. Add a SQL login to a server-level role
The following example adds the SQL login Jiao
to the server-level role ##MS_ServerStateReader##
. This statement has to be run in the virtual master
database.
ALTER SERVER ROLE ##MS_ServerStateReader##
ADD MEMBER Jiao;
GO
B. List all principals (SQL authentication) which are members of a server-level role
The following statement returns all members of any fixed server-level role using the sys.server_role_members
and sys.sql_logins
catalog views. This statement has to be run in the virtual master
database.
SELECT sql_logins.principal_id AS MemberPrincipalID,
sql_logins.name AS MemberPrincipalName,
roles.principal_id AS RolePrincipalID,
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;
GO
C. Complete example: Add a login to a server-level role, retrieve metadata for role membership and permissions, and run a test query
Part 1: Preparing role membership and user account
Run this command from the virtual master
database.
ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER Jiao;
-- check membership in metadata:
SELECT IS_SRVROLEMEMBER('##MS_ServerStateReader##', 'Jiao');
--> 1 = Yes
SELECT sql_logins.principal_id AS MemberPrincipalID,
sql_logins.name AS MemberPrincipalName,
roles.principal_id AS RolePrincipalID,
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;
GO
Here's the result set.
MemberPrincipalID MemberPrincipalName RolePrincipalID RolePrincipalName
------------- ------------- ------------------ -----------
6 Jiao 11 ##MS_ServerStateReader##
Run this command from a user database.
-- Create a database-User for 'Jiao'
CREATE USER Jiao
FROM LOGIN Jiao;
GO
Part 2: Testing role membership
Log in as login Jiao
and connect to the user database used in the example.
-- retrieve server-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'Server');
-- check server-role membership for `##MS_ServerStateReader##` of currently logged on User
SELECT USER_NAME(), IS_SRVROLEMEMBER('##MS_ServerStateReader##');
--> 1 = Yes
-- Does the currently logged in User have the `VIEW DATABASE STATE`-permission?
SELECT HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'VIEW DATABASE STATE');
--> 1 = Yes
-- retrieve database-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');
GO
-- example query:
SELECT * FROM sys.dm_exec_query_stats;
--> will return data since this user has the necessary permission
D. Check server-level roles for Microsoft Entra logins
Run this command in the virtual master
database to see all Microsoft Entra logins that are part of server-level roles in SQL Database. For more information on Microsoft Entra server logins, see Microsoft Entra server principals.
SELECT member.principal_id AS MemberPrincipalID,
member.name AS MemberPrincipalName,
roles.principal_id AS RolePrincipalID,
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.server_principals AS member
ON server_role_members.member_principal_id = member.principal_id
LEFT JOIN sys.sql_logins AS sql_logins
ON server_role_members.member_principal_id = sql_logins.principal_id
WHERE member.principal_id NOT IN (
-- prevent SQL Logins from interfering with resultset
SELECT principal_id
FROM sys.sql_logins AS sql_logins
WHERE member.principal_id = sql_logins.principal_id
);
E. Check the virtual master
database roles for specific logins
Run this command in the virtual master
database to check with roles bob
has, or change the value to match your principal.
SELECT DR1.name AS DbRoleName,
ISNULL(DR2.name, 'No members') AS DbUserName
FROM sys.database_role_members AS DbRMem
RIGHT JOIN sys.database_principals AS DR1
ON DbRMem.role_principal_id = DR1.principal_id
LEFT JOIN sys.database_principals AS DR2
ON DbRMem.member_principal_id = DR2.principal_id
WHERE DR1.type = 'R'
AND DR2.name LIKE 'bob%';
Limitations of server-level roles
Role assignments might take up to 5 minutes to become effective. Also for existing sessions, changes to server role assignments don't take effect until the connection is closed and reopened. This is due to the distributed architecture between the
master
database and other databases on the same logical server.- Partial workaround: to reduce the waiting period and ensure that server role assignments are current in a database, a server administrator, or a Microsoft Entra administrator can run
DBCC FLUSHAUTHCACHE
in the user databases on which the login has access. Current logged on users still have to reconnect after runningDBCC FLUSHAUTHCACHE
for the membership changes to take effect on them.
- Partial workaround: to reduce the waiting period and ensure that server role assignments are current in a database, a server administrator, or a Microsoft Entra administrator can run
IS_SRVROLEMEMBER()
isn't supported in themaster
database.