New granular permissions - SQL Server 2022

SQL Server 2022 introduces new granular permissions that provide more fine-grained control over database objects and actions. It improved the existing set of permissions, by making them more granular. This has happened in 3 distinguishable areas:
  1. Access to System Metadata: 10 new permissions (5 on Server- and 5 on Database level)
  2. Extended Events: 18 new permissions (9 on Server- and 9 on Database level)
  3. Security-related objects: 4 new permissions (3 on Server- and 1 on Database level)

Access to System Metadata:

The new "ACCESS ANY SYSTEM METADATA" permission allows a user to view any system metadata, including system tables, views, and functions. This permission can be granted at the server or database level, and it provides a more fine-grained control over who can access system metadata.

There is a split in existing permissions into two separate sub-permissions below VIEW SERVER STATE/VIEW DATABASE STATE respectively VIEW ANY DEFINITION/VIEW DEFINITION.

The diagram below depicts this change/additional granularity:

Diagram: Split of VIEW SERVER/DATABASE STATE into VIEW SERVER/DATABASE PERFORMANCE STATE and VIEW SERVER/DATABASE SECURITY STATE


Diagram: Split of VIEW DEFINITION/ANY DEFINITION into VIEW ANY SECURITY DEFINITION and SECURITY DEFINITION as well as a new permission VIEW CRYPTOGRAPHICALLY SECURED DEFINITION/ANY CRYPTOGRAPHICALLY SECURED DEFINITION

There are 10 new permissions introduced (5 on server plus 5 on database level):

Server level:

  • VIEW ANY SECURITY DEFINITION
  • VIEW ANY PERFORMANCE DEFINITION
  • VIEW SERVER SECURITY STATE
  • VIEW SERVER PERFORMANCE STATE
  • VIEW ANY CRYPTOGRAPHICALLY SECURED DEFINITION

Database level:

  • VIEW DATABASE SECURITY STATE
  • VIEW DATABASE PERFORMANCE STATE
  • VIEW SECURITY DEFINITION
  • VIEW PERFORMANCE DEFINITION
  • VIEW CRYPTOGRAPHICALLY SECURED DEFINITION

Extended Events:

The "ALTER ANY EVENT SESSION" permission allows a user to modify any extended event session in the database. Extended events are used to collect diagnostic information about SQL Server instances, and this permission can be useful for troubleshooting purposes. This permission can also be granted at the server or database level.

Starting with SQL Server 2022, XEvent session management can be controlled by 18 additional permissions (9 on server plus 9 on database level). This allows for a much more fine-grained control over what a user is allowed to do with XEvent sessions.

The list of new 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

All these permissions are under the same parent-permission: ALTER ANY EVENT SESSION

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

All these permissions are under the same parent-permission: ALTER ANY DATABASE EVENT SESSION

Security-related Objects:

4 new permissions (3 on Server- and 1 on Database level). Here are the 4 new permissions:

  • CREATE LOGIN
  • VIEW ANY ERROR LOG
  • VIEW SERVER SECURITY AUDIT
  • VIEW DATABASE SECURITY AUDIT

Comments

Post a Comment

Popular posts from this blog

COPILOT Feature in SQL Server 2025

Image
 Copilot Feature in SQL Server 2025: A Game-Changer for Database Management As organizations continue to embrace digital transformation, the demand for smarter, more efficient database management tools is growing. Enter SQL Server 2025, Microsoft's latest iteration of its flagship database system, which introduces a revolutionary feature—Copilot. Powered by advanced AI capabilities, Copilot is set to redefine how database administrators (DBAs) and developers interact with SQL Server. What is Copilot in SQL Server 2025? Copilot is an AI-powered assistant integrated into SQL Server 2025. Designed to augment productivity, it leverages natural language processing (NLP) and machine learning (ML) to assist users in managing databases, writing queries, optimizing performance, and ensuring data security. Built on Microsoft's Azure OpenAI service, Copilot brings intelligence directly into the SQL Server ecosystem. Key Features of Copilot Natural Language Querying : Users can write quer...
Read more

Prefetching - SQL Server

Prefetching is a technique used by SQL Server to improve query performance by anticipating the data that will be needed for a query and reading it into memory in advance. This reduces the number of I/O operations required to fetch data from disk, which can significantly improve query performance. There are several mechanisms that SQL Server uses for prefetching, including: Read-Ahead : This mechanism anticipates the data pages that will be needed for a query and reads them from disk into memory in advance. This is based on a predictive algorithm that uses information from the query execution plan to determine which pages are likely to be needed. Pre-Fetching : This mechanism anticipates the related data that will be needed for a query and reads it from related tables into memory in advance. This is based on the relationships defined in the query and can significantly reduce the number of I/O operations required during query execution. Parallelism : SQL Server can use multiple threads ...
Read more

Split Brain - SQL Server

Split Brain is a term used in SQL Server clustering to describe a scenario where the nodes in the cluster lose communication with each other, and each node believes that it is the only active node in the cluster. This can cause multiple issues, including data corruption, duplicate transactions, and other unexpected behaviors. Here's an in-depth detail of split brain in SQL Server clustering with examples: In a SQL Server clustering environment, there are multiple nodes that are connected to each other via a network. Each node has access to the same shared storage, which contains the SQL Server database files. In a normal situation, the nodes communicate with each other to ensure that only one node is active at any given time. This is achieved through a mechanism called a cluster heartbeat, where the nodes send signals to each other to indicate that they are still alive and functioning. However, in certain scenarios, the cluster heartbeat may fail, and the nodes may lose communicat...
Read more