Server-level roles
Applies to: 
SQL Server
Azure SQL Managed Instance
Analytics Platform System (PDW)
SQL Server provides server-level roles to help you manage the permissions on a server. These roles are security principals that group other principals. Server-level roles are server-wide in their permissions scope. (Roles are like groups in the Windows operating system.)
SQL Server 2019 and previous versions provided nine fixed server roles. The permissions that are granted to the fixed server roles (except public) can't be changed. Beginning with SQL Server 2012 (11.x), you can create user-defined server roles and add server-level permissions to the user-defined server roles.
SQL Server 2022 (16.x) comes with 10 extra server roles that have been designed specifically with the Principle of Least Privilege in mind, which have the prefix ##MS_ and the suffix ## to distinguish them from other regular user-created principals and custom server roles. Those new roles contain privileges that apply on server scope but also can inherit down to individual databases (except for the ##MS_LoginManager## server role.)
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## (starting with SQL Server 2022 (16.x)), which grants the CONNECT permission to all databases, or have a user account in individual databases. This also applies to the master database.
Consider the following example: The server-level role ##MS_ServerStateReader## holds the permission VIEW SERVER STATE. A login who is member of this role has a user account in the databases, master and WideWorldImporters. This user will then also have the permission, VIEW DATABASE STATE in those two databases by inheritance.
You can add server-level principals (SQL Server logins, Windows accounts, and Windows groups) into server-level roles. Each member of a fixed server role can add other logins to that same role. Members of user-defined server roles can't add other server principals to the role.
Fixed server-level roles
Note
These server-level roles introduced prior to SQL Server 2022 (16.x) are not available in Azure SQL Database or Azure Synapse Analytics. There are special Azure SQL Database server roles for permission management that are equivalent to the server-level roles introduced in SQL Server 2022 (16.x). For more information about SQL Database, see Controlling and granting database access..
The following table shows the fixed server-level roles and their capabilities.
| Fixed server-level role | Description |
|---|---|
| sysadmin | Members of the sysadmin fixed server role can perform any activity in the server. |
| serveradmin | Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server. |
| securityadmin | Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. securityadmin can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, securityadmin can reset passwords for SQL Server logins.IMPORTANT: The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role. As an alternative, starting with SQL Server 2022 (16.x), consider using the new fixed server role ##MS_LoginManager##. |
| processadmin | Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server. |
| setupadmin | Members of the setupadmin fixed server role can add and remove linked servers by using Transact-SQL statements. (sysadmin membership is needed when using Management Studio.) |
| bulkadmin | Members of the bulkadmin fixed server role can run the BULK INSERT statement.The bulkadmin role or ADMINISTER BULK OPERATIONS permissions isn't supported for SQL Server on Linux. Only the sysadmin can perform bulk inserts for SQL Server on Linux. |
| diskadmin | The diskadmin fixed server role is used for managing disk files. |
| dbcreator | Members of the dbcreator fixed server role can create, alter, drop, and restore any database. |
| public | Every SQL Server login belongs to the public server role. When a server principal hasn't been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users. You can't change membership in public. Note: public is implemented differently than other roles, and permissions can be granted, denied, or revoked from the public fixed server roles. |
Important
Most of the permissions provided by the following server roles are not applicable to Azure Synapse Analytics - processadmin, serveradmin, setupadmin, and diskadmin.
Fixed server-level roles introduced in SQL Server 2022
The following table shows additional fixed server-level roles that are introduced with SQL Server 2022 (16.x) and their capabilities.
Note
These server-level permissions are not available for Azure SQL Managed Instance or Azure Synapse Analytics. ##MS_PerformanceDefinitionReader##, ##MS_ServerPerformanceStateReader##, and ##MS_ServerSecurityStateReader## is introduced in SQL Server 2022 (16.x), and are not available in Azure SQL Database.
| 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_LoginManager## | Members of the ##MS_LoginManager## fixed server role can create, delete, and modify logins. Contrary to the old fixed server role securityadmin, this role doesn't allow members to GRANT privileges. It's a more limited role that helps to comply with the Principle of least Privilege. |
| ##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. This server role has the same privileges as the dbcreator role in SQL Server, but we recommend using this new role over the former, since this role exists also in Azure SQL Database and thus helps using the same scripts across different environments. |
| ##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, and respectively has VIEW DATABASE STATE permission on any database on which the member of this role has a user account. |
| ##MS_ServerPerformanceStateReader## | Members of the ##MS_ServerPerformanceStateReader## fixed server role can read all dynamic management views (DMVs) and functions that are covered by VIEW SERVER PERFORMANCE STATE, and respectively has VIEW DATABASE PERFORMANCE STATE permission on any database on which the member of this role has a user account. This is a subset of what the ##MS_ServerStateReader## server role has access to which helps to comply with the Principle of least Privilege. |
| ##MS_ServerSecurityStateReader## | Members of the ##MS_ServerSecurityStateReader## fixed server role can read all dynamic management views (DMVs) and functions that are covered by VIEW SERVER SECURITY STATE, and respectively has VIEW DATABASE SECURITY STATE permission on any database on which the member of this role has a user account. This is a small subset of what the ##MS_ServerStateReader## server role has access to, which helps to comply with the Principle of least Privilege. |
| ##MS_DefinitionReader## | Members of the ##MS_DefinitionReader## fixed server role can read all catalog views that are covered by VIEW ANY DEFINITION, and respectively has VIEW DEFINITION permission on any database on which the member of this role has a user account. |
| ##MS_PerformanceDefinitionReader## | Members of the ##MS_PerformanceDefinitionReader## fixed server role can read all catalog views that are covered by VIEW ANY PERFORMANCE DEFINITION, and respectively has VIEW PERFORMANCE DEFINITION permission on any database on which the member of this role has a user account. This is a subset of what the ##MS_DefinitionReader## server role has access to. |
| ##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 which helps to comply with the Principle of least Privilege. |
Permissions of fixed server roles
Each fixed server role has certain permissions assigned to it.
Permissions of new fixed server roles in SQL Server 2022
The following table shows the permissions assigned to the server-level roles. It also shows the database-level permissions that are inherited as long as the user can connect to individual databases.
| 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 for SQL Server 2019 and earlier
The following graphic shows the permissions assigned to the legacy server roles (SQL Server 2019 and earlier versions).
Important
The CONTROL SERVER permission is similar but not identical to the sysadmin fixed server role. Permissions do not imply role memberships and role memberships do not grant permissions. (E.g. CONTROL SERVER does not imply membership in the sysadmin fixed server role.) However, it is sometimes possible to impersonate between roles and equivalent permissions. Most DBCC commands and many system procedures require membership in the sysadmin fixed server role.
Server-level permissions
Only server-level permissions can be added to user-defined server roles. To list the server-level permissions, execute the following statement. The server-level permissions are:
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;
For more information about permissions, see Permissions (Database Engine) and sys.fn_builtin_permissions (Transact-SQL).
Work with server-level roles
The following table explains the commands, views, and functions that you can use to work with server-level roles.
| Feature | Type | Description |
|---|---|---|
| sp_helpsrvrole (Transact-SQL) | Metadata | Returns a list of server-level roles. |
| sp_helpsrvrolemember (Transact-SQL) | Metadata | Returns information about the members of a server-level role. |
| sp_srvrolepermission (Transact-SQL) | Metadata | Displays the permissions of a server-level role. |
| IS_SRVROLEMEMBER (Transact-SQL) | Metadata | Indicates whether a SQL Server login is a member of the specified server-level role. |
| sys.server_role_members (Transact-SQL) | Metadata | Returns one row for each member of each server-level role. |
| CREATE SERVER ROLE (Transact-SQL) | Command | Creates a user-defined server role. |
| ALTER SERVER ROLE (Transact-SQL) | Command | Changes the membership of a server role or changes name of a user-defined server role. |
| DROP SERVER ROLE (Transact-SQL) | Command | Removes a user-defined server role. |
| sp_addsrvrolemember (Transact-SQL) | Command | Adds a login as a member of a server-level role. Deprecated. Use ALTER SERVER ROLE instead. |
| sp_dropsrvrolemember (Transact-SQL) | Command | Removes a SQL Server login or a Windows user or group from a server-level role. Deprecated. Use ALTER SERVER ROLE instead. |
Roles specific to SQL Server enabled by Azure Arc
When you install Azure extension for SQL Server, the installation:
Creates a server level role: SQLArcExtensionServerRole
Creates a database level role: SQLArcExtensionUserRole
Adds NT AUTHORITY\SYSTEM* account to each role
Maps NT AUTHORITY\SYSTEM* at the database level for each database
Grants minimum permissions for the enabled features
*Alternatively, you can configure SQL Server enabled by Azure Arc to run in least privilege mode (available in preview). For details, review Operate SQL Server enabled by Azure Arc with least privilege (preview).
In addition, Azure extension for SQL Server revokes permissions for these roles when they're no longer needed for specific features.
SqlServerExtensionPermissionProvider is a Windows task. It grants or revokes privileges in SQL Server when it detects:
- A new SQL Server instance is installed on the host
- SQL Server instance is uninstalled from host
- An instance level feature is enabled or disabled or settings are updated
- Extension service is restarted
Note
Prior to the July, 2024 release, SqlServerExtensionPermissionProvider is a scheduled task. It runs hourly.
For details, review Configure Windows service accounts and permissions for Azure extension for SQL Server.
If you uninstall Azure extension for SQL Server, the server and database level roles are removed.
For permissions, review Permissions.
Related content
- Database-Level Roles
- Security Catalog Views (Transact-SQL)
- Security Functions (Transact-SQL)
- Securing SQL Server
- GRANT Server Principal Permissions (Transact-SQL)
- REVOKE Server Principal Permissions (Transact-SQL)
- DENY Server Principal Permissions (Transact-SQL)
- Create a Server Role
- Azure SQL Database server roles for permission management