SQL Server employs server-level roles as a cornerstone of its security framework, simplifying the management of permissions across your server environment. These roles act as security principles, grouping logins and other server principals to streamline permission assignments. Operating at the server level, their scope of influence extends server-wide, much like groups function within Windows operating systems.
For SQL Server versions up to 2019 (15.x), nine fixed server roles were the standard. These roles, with the exception of public, came with pre-defined permissions that were immutable. SQL Server 2012 (11.x) introduced the flexibility of user-defined server roles, allowing administrators to tailor permissions to specific needs. SQL Server 2022 (16.x) further refined this model by adding ten new server roles, prefixed with ##MS_ and suffixed with ##, meticulously designed around the Principle of Least Privilege. These newer roles offer granular control over server-level privileges, many of which can also cascade down to individual databases (excluding the ##MS_LoginManager## role). This refined approach to server roles is critical for robust Security Server Login management.
Similar to on-premises SQL Server deployments, server permissions are structured hierarchically. Permissions assigned at the server level through these roles can propagate to the database level. For these inherited permissions to be effective within a specific database, a login must either be a member of the ##MS_DatabaseConnector## server role (introduced in SQL Server 2022 (16.x)), which grants universal CONNECT permission across all databases, or possess a dedicated user account within each database, including the master database. This mechanism ensures controlled and secure server login security.
Consider this practical scenario: A login is made a member of the ##MS_ServerStateReader## server role, which inherently holds the VIEW SERVER STATE permission. If this login has corresponding user accounts in both the master and WideWorldImporters databases, it automatically inherits the VIEW DATABASE STATE permission in both, demonstrating the principle of permission inheritance and its impact on security server login capabilities.
Server-level principals, encompassing SQL Server logins, Windows accounts, and Windows groups, can be incorporated into server-level roles. Within fixed server roles, any member has the authority to add other logins to the same role. However, this capability is restricted for user-defined server roles; their members cannot add other server principals. This distinction is important for maintaining security server login protocols and role integrity.
Understanding Fixed Server-Level Roles
Note: The server-level roles detailed below, introduced prior to SQL Server 2022 (16.x), are not available in Azure SQL Database or Azure Synapse Analytics. Azure SQL Database provides specialized server roles for permission management that mirror the server-level roles introduced in SQL Server 2022 (16.x). For a comprehensive understanding of SQL Database security, refer to Controlling and granting database access..
The following table outlines the fixed server-level roles and their respective functionalities, crucial for managing security server login access in SQL Server.
| Fixed server-level role | Description |
|---|---|
| sysadmin | Members of the sysadmin fixed server role possess unrestricted capabilities within the server. Critically, permissions cannot be denied to members of this role, making it the highest level of privilege. |
| serveradmin | The serveradmin role grants the ability to manage server-wide configuration options and to shut down the server. |
| securityadmin | Members of the securityadmin role are responsible for managing logins and their associated properties. They can GRANT, DENY, and REVOKE server-level permissions. If they have database access, securityadmin can also manage database-level permissions. Furthermore, they can reset passwords for SQL Server logins. IMPORTANT: The extensive permissions granted to securityadmin, including access control to the Database Engine and configuration of user permissions, effectively equate this role to sysadmin in terms of potential impact. For enhanced security server login management, especially in SQL Server 2022 (16.x) and later, consider leveraging the new ##MS_LoginManager## role as a more restrictive alternative. |
| processadmin | The processadmin role is empowered to terminate processes running within a SQL Server instance. |
| setupadmin | Members of the setupadmin role can manage linked servers using Transact-SQL statements. Note that sysadmin membership is required when using SQL Server Management Studio for linked server management. |
| bulkadmin | The bulkadmin role permits the execution of the BULK INSERT statement for high-volume data import operations. It’s important to note that the bulkadmin role and ADMINISTER BULK OPERATIONS permissions are not supported for SQL Server on Linux. On Linux, only sysadmin can perform bulk inserts. |
| diskadmin | The diskadmin fixed server role is specifically designed for managing disk files related to SQL Server. |
| dbcreator | Members of the dbcreator role can create, alter, drop, and restore databases, providing comprehensive control over database lifecycle management. |
| public | Every SQL Server login automatically belongs to the public server role. When a server principal lacks explicit permissions (granted or denied) on a securable object, they inherit the permissions assigned to public for that object. Permissions should only be assigned to public when universal access to an object is intended. Membership in public is immutable and cannot be altered. Note: public operates uniquely compared to other roles. Permissions can be granted to, denied to, or revoked from the public fixed server role, making it a foundational element in security server login configurations. |
Important: Many permissions associated with the following server roles are not applicable to Azure Synapse Analytics: processadmin, serveradmin, setupadmin, and diskadmin.
New Fixed Server-Level Roles in SQL Server 2022
SQL Server 2022 (16.x) introduced a set of refined fixed server-level roles, designed to enhance security server login management by adhering to the principle of least privilege.
Note: These server-level permissions are not available in Azure SQL Managed Instance or Azure Synapse Analytics. Furthermore,
##MS_PerformanceDefinitionReader##,##MS_ServerPerformanceStateReader##, and##MS_ServerSecurityStateReader##are exclusive to SQL Server 2022 (16.x) and are not present in Azure SQL Database.
| Fixed server-level role | Description |
|---|---|
##MS_DatabaseConnector## |
Members of the ##MS_DatabaseConnector## role can connect to any database without needing a dedicated user account within each database. To restrict CONNECT permission to a specific database, a user account for the login can be created in that database, and then DENY CONNECT permission can be explicitly applied to that database user. This DENY action overrides the GRANT CONNECT permission originating from this server role, offering a granular approach to security server login control. |
##MS_LoginManager## |
The ##MS_LoginManager## role empowers members to create, delete, and modify logins. In contrast to the legacy securityadmin role, ##MS_LoginManager## does not grant the ability to GRANT privileges. This more restrictive design aligns with the Principle of Least Privilege, providing a safer approach to managing security server login credentials. |
##MS_DatabaseManager## |
Members of the ##MS_DatabaseManager## role can create and delete databases. A user in this role who creates a database automatically becomes the owner (dbo) of that database, granting them full database permissions. However, membership in ##MS_DatabaseManager## does not inherently grant access to databases they do not own. This role mirrors the privileges of the older dbcreator role in SQL Server but is recommended for broader use as it is also available in Azure SQL Database, promoting consistency across environments and enhancing security server login management across platforms. |
##MS_ServerStateManager## |
The ##MS_ServerStateManager## role encompasses all permissions of the ##MS_ServerStateReader## role and additionally includes the ALTER SERVER STATE permission. This expanded permission set allows access to various server management operations, including DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE ('ALL'), and DBCC SQLPERF(). |
##MS_ServerStateReader## |
Members of the ##MS_ServerStateReader## role can access all dynamic management views (DMVs) and functions covered by VIEW SERVER STATE. Correspondingly, they inherit VIEW DATABASE STATE permission on any database where they have a user account, facilitating server monitoring and diagnostics related to security server login activities. |
##MS_ServerPerformanceStateReader## |
This role grants access to DMVs and functions covered by VIEW SERVER PERFORMANCE STATE. Members also inherit VIEW DATABASE PERFORMANCE STATE permission in databases where they have user accounts. ##MS_ServerPerformanceStateReader## is a subset of ##MS_ServerStateReader##, adhering to the Principle of Least Privilege by providing narrower performance-focused monitoring capabilities for security server login analysis. |
##MS_ServerSecurityStateReader## |
Members of ##MS_ServerSecurityStateReader## can read DMVs and functions under VIEW SERVER SECURITY STATE, and inherit VIEW DATABASE SECURITY STATE permission in relevant databases. This role offers a focused view of security-related server state, being a smaller subset of ##MS_ServerStateReader## and further embodying the Principle of Least Privilege in security server login monitoring. |
##MS_DefinitionReader## |
The ##MS_DefinitionReader## role allows members to read catalog views covered by VIEW ANY DEFINITION and inherit VIEW DEFINITION permission in databases where they have accounts. This role provides broad access to database schema and object definitions for auditing and analysis of security server login configurations. |
##MS_PerformanceDefinitionReader## |
Members of ##MS_PerformanceDefinitionReader## can read catalog views covered by VIEW ANY PERFORMANCE DEFINITION and inherit VIEW PERFORMANCE DEFINITION permission in databases with user accounts. It’s a subset of ##MS_DefinitionReader##, focused on performance-related definitions, again emphasizing least privilege in accessing information pertinent to security server login performance analysis. |
##MS_SecurityDefinitionReader## |
This role grants access to catalog views covered by VIEW ANY SECURITY DEFINITION and inherited VIEW SECURITY DEFINITION permission in databases with user accounts. ##MS_SecurityDefinitionReader## is the most narrowly scoped definition reader role, a subset of ##MS_DefinitionReader##, specifically for security-related definitions, and aligns with the principle of least privilege when managing access for security server login auditing and compliance. |
Permissions Associated with Fixed Server Roles
Each fixed server role is pre-configured with a specific set of permissions.
Permissions of New Fixed Server Roles (SQL Server 2022)
The following table details the server-level permissions directly assigned to the new server roles introduced in SQL Server 2022, as well as the database-level permissions that are inherited when a user connects to individual databases. This mapping is crucial for understanding the effective permissions granted through these roles in the context of security server login.
| Fixed server-level role | Server-level permissions | Database-level permissions |
|---|---|---|
##MS_DatabaseConnector## |
– CONNECT ANY DATABASE |
– CONNECT |
##MS_LoginManager## |
– CREATE LOGIN– ALTER ANY LOGIN |
N/A |
##MS_DatabaseManager## |
– CREATE ANY DATABASE– ALTER ANY DATABASE |
– ALTER |
##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 |
##MS_ServerPerformanceStateReader## |
– VIEW SERVER PERFORMANCE STATE |
– VIEW DATABASE PERFORMANCE STATE |
##MS_ServerSecurityStateReader## |
– VIEW SERVER SECURITY STATE |
– VIEW DATABASE SECURITY STATE |
##MS_DefinitionReader## |
– VIEW ANY DATABASE– VIEW ANY DEFINITION– VIEW ANY PERFORMANCE DEFINITION– VIEW ANY SECURITY DEFINITION |
– VIEW DEFINITION– VIEW PERFORMANCE DEFINITION– VIEW SECURITY DEFINITION |
##MS_PerformanceDefinitionReader## |
– VIEW ANY PERFORMANCE DEFINITION |
– VIEW PERFORMANCE DEFINITION |
##MS_SecurityDefinitionReader## |
– VIEW ANY SECURITY DEFINITION |
– VIEW SECURITY DEFINITION |
Permissions of Server Roles (SQL Server 2019 and Earlier)
The graphic below illustrates the permissions assigned to the legacy server roles in SQL Server 2019 (15.x) and earlier. Understanding these permissions is vital for managing security server login in older SQL Server environments.
Diagram showing fixed server role permissions.
While the CONTROL SERVER permission shares similarities with the sysadmin role, they are not identical. Principals with CONTROL SERVER can have specific permissions denied, unlike sysadmin. However, from a security standpoint, entities with CONTROL SERVER should be considered nearly equivalent to sysadmin members due to potential privilege escalation paths. Notably, numerous DBCC commands and system procedures mandate membership in the sysadmin fixed server role. Therefore, careful management of these high-privilege roles is paramount for robust security server login practices.
Server-Level Permissions in Detail
Only server-level permissions can be granted to user-defined server roles. To obtain a comprehensive list of server-level permissions, execute the following SQL query:
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;For in-depth information about permissions, consult Permissions (Database Engine) and sys.fn_builtin_permissions. A thorough understanding of these permissions is essential for effective security server login management and custom role design.
Managing Server-Level Roles
The subsequent table outlines the commands, views, and functions available for managing server-level roles, which are indispensable for administering security server login configurations.
| Feature | Type | Description |
|---|---|---|
| sp_helpsrvrole | Metadata | Lists all server-level roles. |
| sp_helpsrvrolemember | Metadata | Provides details about the members of a specified server-level role. |
| sp_srvrolepermission | Metadata | Displays the permissions associated with a server-level role. |
| IS_SRVROLEMEMBER | Metadata | Checks if a SQL Server login is a member of a given server-level role, useful for verifying security server login role assignments. |
| sys.server_role_members | Metadata | Returns a row for each member of every server-level role, offering a comprehensive view of role memberships. |
| CREATE SERVER ROLE | Command | Creates a new user-defined server role for customized security server login management. |
| ALTER SERVER ROLE | Command | Modifies the membership of a server role or renames a user-defined server role, essential for dynamic security server login adjustments. |
| DROP SERVER ROLE | Command | Deletes a user-defined server role, necessary for removing obsolete security server login configurations. |
| sp_addsrvrolemember | Command | Adds a login as a member to a server-level role. Deprecated; use ALTER SERVER ROLE instead for modern security server login management practices. |
| sp_dropsrvrolemember | Command | Removes a SQL Server login, Windows user, or group from a server-level role. Deprecated; use ALTER SERVER ROLE instead for up-to-date security server login administration. |
Azure Arc-Specific Roles for SQL Server
When the Azure extension for SQL Server is installed, it introduces specific roles to manage integration with Azure Arc:
-
SQLArcExtensionServerRole: A server-level role created to manage Azure Arc extension functionalities.
-
SQLArcExtensionUserRole: A database-level role for database-specific Azure Arc extension operations.
-
The installation process automatically adds the
NT AUTHORITYSYSTEM*account to both roles. -
NT AUTHORITYSYSTEM*is mapped at the database level for each database. -
Minimum permissions are granted to these roles, tailored to the enabled Azure Arc features.
*Alternatively, SQL Server enabled by Azure Arc can be configured to operate in least privilege mode (preview). Refer to Operate SQL Server enabled by Azure Arc with least privilege (preview) for detailed information on enhancing security server login in Azure Arc environments.
The Azure extension for SQL Server dynamically revokes permissions for these roles when they are no longer required for specific features, ensuring ongoing security server login optimization.
SqlServerExtensionPermissionProvider, a Windows task, manages permission adjustments in SQL Server. It grants or revokes privileges when it detects:
- Installation of a new SQL Server instance on the host.
- Uninstallation of a SQL Server instance.
- Enabling, disabling, or updating settings for an instance-level feature.
- Restart of the extension service.
Note: Before the July 2024 release,
SqlServerExtensionPermissionProviderwas a scheduled task running hourly.
For comprehensive details, review Configure Windows service accounts and permissions for Azure extension for SQL Server, which provides further insights into security server login considerations within Azure Arc.
Uninstalling the Azure extension for SQL Server removes both the server and database level roles associated with it, reverting any Azure Arc specific security server login configurations.
For detailed permission information, refer to Permissions.
