New SQL 2022 Server and Database Roles and Permissions

Well…..I just learned about these and thought that it would be good to understand them a little more and have some links to read more about them.  I honestly don’t have a lot of SQL 2022 servers in our customers SQL estate, so this has flown under the radar for me.  This will be an attempt to put some spread-out information in a one-stop shop.

The new server and database roles will allow for more granular permissions.  Here is a combined list of the new fixed server roles and their permissions.

Fixed server-level role	Description	Server-level permissions	Database-level permissions
##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.	CONNECT ANY DATABASE	CONNECT
##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.	CREATE LOGIN	N/A
		ALTER ANY LOGIN
##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.	CREATE ANY DATABASE	ALTER
		ALTER ANY DATABASE
##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()	ALTER SERVER STATE	VIEW DATABASE STATE
		VIEW SERVER STATE	VIEW DATABASE PERFORMANCE STATE
		VIEW SERVER PERFORMANCE STATE	VIEW DATABASE SECURITY STATE
		VIEW SERVER SECURITY STATE
##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.	VIEW SERVER STATE	VIEW DATABASE STATE
		VIEW SERVER PERFORMANCE STATE	VIEW DATABASE PERFORMANCE STATE
		VIEW SERVER SECURITY STATE	VIEW DATABASE SECURITY STATE
##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.	VIEW SERVER PERFORMANCE STATE	VIEW DATABASE PERFORMANCE STATE
##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.	VIEW SERVER SECURITY STATE	VIEW DATABASE SECURITY STATE
##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.	VIEW ANY DATABASE	VIEW DEFINITION
		VIEW ANY DEFINITION	VIEW PERFORMANCE DEFINITION
		VIEW ANY PERFORMANCE DEFINITION	VIEW SECURITY DEFINITION
		VIEW ANY SECURITY DEFINITION
##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.	VIEW ANY PERFORMANCE DEFINITION	VIEW PERFORMANCE DEFINITION
##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.	VIEW ANY SECURITY DEFINITION	VIEW SECURITY DEFINITION

New granular permissions

• 10 new permissions have been added to allow access to system metadata.
• 18 new permissions have been added for extended events.
• 9 new permissions have been added with regard to security-related objects.
• 4 permissions have been added for Ledger.
• 3 additional database permissions.

Metadata permissions

Server level:

• VIEW ANY SECURITY DEFINITION
• VIEW ANY PERFORMANCE DEFINITION
• VIEW SERVER SECURITY STATE
• VIEW SERVER PERFORMANCE STATE
• VIEW ANY CRYPTOGRAPHICALLY SECURED DEFINITION – access to specific columns in some objects

Database level:

• VIEW DATABASE SECURITY STATE
• VIEW DATABASE PERFORMANCE STATE
• VIEW SECURITY DEFINITION
• VIEW PERFORMANCE DEFINITION
• VIEW CRYPTOGRAPHICALLY SECURED DEFINITION – access to specific columns in some objects

Extended Events permissions

Server level:

• CREATE ANY EVENT SESSION
• DROP ANY EVENT SESSION
• ALTER ANY EVENT SESSION OPTION
• ALTER ANY EVENT SESSION ADD EVENT
• ALTER ANY EVENT SESSION DROP EVENT
• ALTER ANY EVENT SESSION ENABLE
• ALTER ANY EVENT SESSION DISABLE
• ALTER ANY EVENT SESSION ADD TARGET
• ALTER ANY EVENT SESSION DROP TARGET

Database level:

• CREATE ANY DATABASE EVENT SESSION
• DROP ANY DATABASE EVENT SESSION
• ALTER ANY DATABASE EVENT SESSION OPTION
• ALTER ANY DATABASE EVENT SESSION ADD EVENT
• ALTER ANY DATABASE EVENT SESSION DROP EVENT
• ALTER ANY DATABASE EVENT SESSION ENABLE
• ALTER ANY DATABASE EVENT SESSION DISABLE
• ALTER ANY DATABASE EVENT SESSION ADD TARGET
• ALTER ANY DATABASE EVENT SESSION DROP TARGET

Security

• CONTROL (CREDENTIAL)
• CREATE LOGIN
• CREATE USER
• REFERENCES (CREDENTIAL)
• UNMASK (OBJECT)
• UNMASK (SCHEMA)
• VIEW ANY ERROR LOG
• VIEW SERVER SECURITY AUDIT
• VIEW DATABASE SECURITY AUDIT

Ledger

https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-overview?view=sql-server-ver16

• ALTER LEDGER
• ALTER LEDGER CONFIGURATION
• ENABLE LEDGER
• VIEW LEDGER CONTENT

Database

• ALTER ANY EXTERNAL JOB
• ALTER ANY EXTERNAL STREAM
• EXECUTE ANY EXTERNAL ENDPOINT

Here are some new columns in sys.databases that show if some of the new things are enabled in your databases.

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-ver16

Server-level roles – SQL Server | Microsoft Learn

https://learn.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver16#new-granular-permissions-added-to-sql-server-2022

New granular permissions for SQL Server 2022 and Azure SQL to improve adherence with PoLP – Microsoft Community Hub

Security: The Principle of Least Privilege (POLP) – Microsoft Community Hub

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!