blog.Mainzer Datenfabrik

Sql Server 2022 New Features: Sprachliche Änderungen, neue Berechtigungen und Rollen

cover image of blog article 'Sql Server 2022 New Features: Sprachliche Änderungen, neue Berechtigungen und Rollen'

Weiter geht es in unserer Artikelreihe zur Vorstellung der neuen Features und Funktionen in SQL Server 2022. In diesem Artikel widmen wir uns den neuen sprachlichen Änderungen, Berechtigungen und Rollen.

Time Series Functions

Daten, die sich im Laufe der Zeit ändern, lassen sich durch Zeitfenster-, Aggregations- und Filterfunktionen einfacher analysieren. Hier einige Beispiele:

  • DATE_BUCKET
    Diese Funktion gibt den Datum-Uhrzeit-Wert zurück, der zu Beginn jedes Buckets durch den “origin” Parameter konfiguriert wurde. Falls initial kein Wert eingestellt wurde, wird der Standardwert “1900-01-01 00:00:00.000“ verwendet.

Syntax:

DATE_BUCKET (datepart, number, date, origin)
  • GENERATE_SERIES
    Erzeugt eine Reihe von Zahlen innerhalb eines bestimmten Intervalls. Die Intervalle werden selbst festgelegt.

Syntax:

GENERATE_SERIES
(
    START = @start | start_literal | numeric_expression
    , STOP = @stop | stop_literal | numeric_expression
    [, STEP = @step | step_literal | numeric_expression ]
)

Das Hinzufügen der folgenden Parameter beeinflusst wie mit NULL Werten umgegangen werden soll:

  • FIRST_VALUE
    Gibt den ersten Wert der geordneten Objekte wieder.

Syntax:

FIRST_VALUE ( [scalar_expression ] )  [ IGNORE NULLS | RESPECT NULLS ]
    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
  • LAST_VALUE
    Gibt den letzten Wert der sortierten Objekte wieder.

Syntax:

LAST_VALUE ( [ scalar_expression ] )  [ IGNORE NULLS | RESPECT NULLS ]
    OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )

SELECT ... WINDOW

Die Partition und Anordnung eines Rowsets können konfiguriert werden, bevor die OVER-Klausel verwendet wird. Die Partitionierung einer Window-Klausel bestimmt die Anordnung dieses Rowsets. Dies kann überschrieben werden, indem man die OVER-Klausel verwendet.

SELECT WINDOW wurde in SQL Server 2022 (16.x) eingeführt.

Die WINDOW-Klausel erfordert Datenbankkompatibilitätsebene 160 oder höher. Wenn diese niedriger als 160 ist, kann SQL Server keine Abfragen mit der WINDOW-Klausel ausführen.

Sie können die Kompatibilitätsebene in der Ansicht sys.databases oder in den Datenbankeigenschaften überprüfen. Falls nicht, kann dies wie folgt angepasst werden.

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 160

Syntax:

WINDOW window_name AS (
       [ reference_window_name ]   
       [ <PARTITION BY clause> ]  
       [ <ORDER BY clause> ]   
       [ <ROW or RANGE clause> ]  
      )  
  
<PARTITION BY clause> ::=  
PARTITION BY value_expression , ... [ n ]  
  
<ORDER BY clause> ::=  
ORDER BY order_by_expression  
    [ COLLATE collation_name ]   
    [ ASC | DESC ]   
    [ ,...n ]  
  
<ROW or RANGE clause> ::=  
{ ROWS | RANGE } <window frame extent>

window_name
Name des Fensters. Dieser Name wird von der Funktion in der OVER-Klausel verwendet, um auf die Spezifikationen zu verweisen.

reference_window_name
Name des Fensters, auf das das aktuelle Fenster verweist. Das referenzierte Fenster muss zu den in der WINDOW-Klausel definierten Fenstern gehören.

Andere Parameter:

PARTITION BY: Die Abfrage wird in Partitionen unterteilt.

ORDER BY: Die logische Reihenfolge der Zeilen und Partitionen wird definiert.

ROWS/RANGE: Begrenzt die Zeilen innerhalb einer Partition, indem er Anfangs- und Endpunkte setzt.

ROWS/RANGE: Ermöglich es das Abfrageergebnis zu begrenzen. Z.B. 1-5 oder 20-30.

T-SQL Funktionen

Folgende neuen Funktionen wurden zu SQL Server hinzugefügt:

  • GREATEST

    • Diese Funktion gibt den maximalen/größten Wert der Abfrage wieder.
    • Der Syntax:
GREATEST ( expression1 [ ,...expressionN ] )
  • expression1, expressionN
    Es ist möglich eine Liste von Parametern anzugeben. Es werden maximal 254 Argumente unterstützt.

    • Jeder Ausdruck kann eine Konstante, eine Variable, ein Spaltenname oder eine Funktion sowie
      eine beliebige Kombination von arithmetischen, bitweisen und String-Operatoren sein.
      Aggregatfunktionen und skalare Unterabfragen können zusätzlich angegeben werden.
  • LEAST

    • Diese Funktion gibt den kleinsten Wert der Abfrage wieder.
    • Der Syntax:
LEAST ( expression1 [ ,...expressionN ] )
  • expression1, expressionN

    • Wie die “GREATEST“ Funktion ist es auch hier möglich weitere Parameter anzugeben - maximal 254 Stück.
  • SPRING_SPLIT

    • Diese Funktion ermöglicht es einen String in verschiedene Substrings aufzuteilen. Hierfür muss man angeben, wie die Zeichenfolge zerlegt werden soll.

    • Die “STRING_SPLIT“ Funktion erfordert eine Kompatibilitätsebene der Datenbank von mindestens 130.

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }
  • Syntax:
STRING_SPLIT ( string , separator [ , enable_ordinal ] )
  • string, beliebiger Zeichentyp (z. B. nvarchar, varchar, nchar oder char).
    • seperator, wird ebenfalls durch einen beliebigen Zeichentyp angegeben (varchar etc.). Der
      angegebene Charakter stellt das Trennzeichen dar.
    • enable_ordinal, dieser optionale Flag wird aktiviert durch ein int- oder Bitausdruck. Wie
      der Name vermuten lässt, wird hiermit die Ausgabe der ordinalen Spalte aktiviert/deaktiviert.
    • Der Wert 1 aktiviert die Ordinalspalte. Wenn kein Wert angegeben wird, wird der 0/NULLL Wert
      verwendet. Die Ordinalspalte ist deaktiviert.

Resumable add table constraints

Ermöglicht das Anhalten und Wiederaufnehmen des “ALTER TABLE ADD CONTRAINT”- Vorgangs.

Dies ist beispielsweise nach einem Wartungsfenster, Failover oder anderen ungeplanten Systemausfällen notwendig.

SQL Server 2022 unterstützt das Wiederaufnehmen von abgebrochenen Operationen wie z.B. Online-Indexerstellung während die Tabelle online ist. Ebenfalls werden folgende Vorgänge unterstützt:

  • Stoppen und Starten der Indexerstellung oder eines Neuaufbaus, um z.B. ein Wartungsfenster einzuhalten.
  • Wiederherstellen von Fehlern bei der Indexerstellung oder dem Neuaufbau, z. B. bei einem Datenbank-Failover oder wenn kein Speicherplatz mehr vorhanden ist.
  • Aktivieren der Trunkierung von Transaktionsprotokollen während einer Indexerstellungs- oder Wiederherstellungsoperation.
  • Wenn ein Indexvorgang angehalten wird, benötigen sowohl der ursprüngliche als auch der neu erstellte Index Speicherplatz und müssen bei DML-Operationen aktualisiert werden.

Resumable operations

In Vorgänger-Versionen von SQL Server kann die Operation ALTER TABLE ADD CONTRAINT mit der Option ONLINE = ON ausgeführt werden. Dies kann jedoch bei großen Tabellen mehrere Stunden und kostbare Ressourcen in Anspruch nehmen.

Für ALTER TABLE ADD CONTRAINT wurde die Möglichkeit eingeführt, den Vorgang während eines Wartungsfenster anzuhalten und ihn an einer anderen Stelle wieder aufzunehmen. Dies ist möglich, ohne den Vorgang komplett neu starten zu müssen.

Folgende Szenarien werden unterstützt:

  • Unterbrechung und Wiederaufnahme der laufenden ALTER TABLE ADD CONSTRAINT Operationen, z.B. wenn durch ein Wartungsfenster die Operation unterbrochen werden muss.
  • Wiederaufnahme der ALTER TABLE ADD CONSTRAINT-Operation nach einem Failover und anderen Systemausfällen.
  • Ausführen der ALTER TABLE ADD CONSTRAINT-Operation auf einer großen Tabelle trotz einer geringen verfügbaren Protokollgröße.

Neue Berechtigungen und Rollen

Aktivieren Sie mit den neuen integrierten Rollen auf Serverebene den geringstmöglichen privilegierten Zugriff für administrative Aufgaben. SQL Server bietet Rollen auf Serverebene, um Sie bei der Verwaltung der Berechtigungen auf einem Server zu unterstützen. Diese Rollen sind Sicherheitsprinzipale, die andere Prinzipale gruppieren. Rollen auf Serverebene sind in ihrem Berechtigungsumfang serverweit.

Rollen sind wie Gruppen im Windows-Betriebssystem.

Berechtigungen sind ein kritisches Thema, wenn es darum geht, eine Datenbank möglichst sicher zu gestalten. Durch die neuen Rollen ist es nun angenehmer ihren Administratoren genauere Berechtigungen zuzuweisen. Dies erhöht Effizienz und verringert die Chance darauf, dass eine Person mehr Berechtigungen hat als sie eigentlich sollte.

Serverrollen eingeführt in SQL Server 2022:

  • MS_DatabaseConnector

    • Mitglieder der Serverrolle MS_DatabaseConnector können sich mit der Datenbank verbinden, ohne jedoch, dass sie einem dedizierten Benutzer in der Datenbank benötigen.
  • MS_DatabaseManager

    • Mitglieder der Serverrolle MS_DatabaseManager können Datenbanken erstellen und löschen.
    • Wenn ein Mitglied der Rolle MS_DatabaseManager eine Datenbank erstellt, wird dieser Benutzer automatisch zum Eigentümer dieser Datenbank. Dadurch hat dieser Benutzer dbo Berechtigungen auf dieser Datenbank.
    • Mitglieder der MS_DatabaseManager haben nicht zwingendermaßen die Berechtigung auf alle Datenbanken zuzugreifen.
  • MS_DefinitionReader

    • Mitglieder der Serverrolle MS_DefinitionReader haben die Möglichkeit alle Katalogansichten zu lesen, die durch die VIEW ANY DEFINITION abgedeckt werden. Kurzgesagt hat das Mitglied die Berechtigung den Befehl VIEW DEFINITION auf jeder Datenbank auszuführen, auf der sie einen Benutzer haben.
  • MS_LoginManager

    • Mitglieder der Rolle MS_LoginManager können Logins erstellen und löschen.
  • MS_SecurityDefinitionReader

    • Mitglieder der Rolle MS_SecurityDefinitionReader können SECURITY DEFINITON von jeder Datenbank einsehen, auf der sie einen Benutzer haben.
  • MS_ServerStateReader

    • Mitglieder der Serverrolle MS_ServerStateReader haben die Berechtigungen VIEW DATABASE STATE auf jeder Datenbank einzusehen, auf der sie einen Benutzer haben.
  • MS_ServerStateManager

    • Mitglieder der festen Serverrolle MS_ServerStateManager haben die gleichen Berechtigungen wie die Rolle MS_ServerStateReader.
    • Zusätzlich verfügt er über die Berechtigung ALTER SERVER STATE, die den Zugriff auf mehreren Verwaltungsoperationen ermöglicht, wie z.B. DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE und DBCC SQLPERF()

Permissions of fixed server roles

Jeder festen Serverrolle sind bestimmte Berechtigungen zugewiesen. Dies sind die neuen festen Serverrollen in SQL Server 2022.

Die folgende Tabelle zeigt die den Rollen auf Serverebene zugewiesenen Berechtigungen. Sie zeigt ebenfalls die geerbten Berechtigungen auf Datenbankebene:

Fixed server-level role Server-level permissions Database-level permissions
MS_DatabaseManager CREATE ANY DATABASE, ALTER ANY DATABASE ALTER
MS_DatabaseConnector CONNECT ANY DATABASE CONNECT
MS_LoginManager CREATE LOGIN ALTER ANY LOGIN N/A
MS_DefinitionReader VIEW ANY DATABASE, VIEW ANY DEFINITION, VIEW ANY SECURITY DEFINITION VIEW DEFINITION, VIEW SECURITY DEFINITION
MS_SecurityDefinitionReader VIEW ANY SECURITY DEFINITION VIEW SECURITY DEFINITION
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_ServerStateManager ALTER SERVER STATE, VIEW SERVER STATE, VIEW SERVER PERFORMANCE STATE, VIEW SERVER PERFORMANCE STATE VIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE

Arbeiten mit Rollen auf Serverebene

In der folgenden Tabelle werden die Befehle, Ansichten und Funktionen erläutert, die Sie für die Arbeit mit Rollen auf Serverebene verwenden können.

Feature Type Description
sp_helpsrvrole Metadata Gibt eine Liste der Serverlevelrollen wieder.
sp_helpsrvrolemember Metadata Gibt Informationen über Mitglieder einer Serverrolle.
sp_srvrolepermission Metadata Zeigt die Berechtigungen einer Rolle.
IS_SRVROLEMEMBER Metadata Zeigt, ob ein Nutzer sich über SQL Server Login angemeldet hat, der Mitglied der bestimmten Rolle ist.
sys.server_role_members Metadata Gibt eine Zeile für jedes Mitglied einer Rolle wieder.
CREATE SERVER ROLE Command Erstellt eine benutzerdefinierte Serverrolle.
ALTER SERVER ROLE Command Erlaubt die Mitglieder oder den Namen der benutzerdefinierten Serverrolle anzupassen.
DROP SERVER ROLE Command Löscht eine benutzerdefinierte Serverrolle.
sp_addsrvrolemember Command Fügt einen Login als Mitglied einer Serverrolle hinzu. Diese Funktion ist veraltet, benutzen Sie stattdessen ALTER SERVER ROLE.
sp_dropsrvrolemember Command Entfernt einen SQL Server Login oder einen Windows Benutzer von einer Serverrolle. Dieser Funktion ist ebenfalls veraltet. Nutzen Sie stattdessen ALTER SERVER ROLE.

Das waren sie - alle neue Serverrollen und Datenbankberechtigungen. Ebenfalls haben wir Ihnen neue Funktionen gezeigt, die es vereinfachen mit Strings zu arbeiten. Trotz der obigen Übersicht ist es nicht immer ganz einfach, die optimalen Berechtigungen für eine neue Datenbank und deren Benutzer zu konfigurieren.

Gerne helfen wir Ihnen dabei! Kontaktieren Sie unverbindlich eine:n Spezialist:in von uns über das Kontaktformular.