Server Roles übertragen

Zweckbeschreibung für das Skript

Das vorliegende Skript dient der generischen Erstellung von Migrationsanweisungen für Serverrollen-Mitgliedschaften zwischen zwei SQL Server Instanzen. Es ersetzt die veraltete gespeicherte Prozedur sp_addsrvrolemember durch die moderne, standardkonforme Syntax ALTER SERVER ROLE ... ADD MEMBER.

Hauptzweck

• Automatische Generierung von T‑SQL‑Befehlen, die auf einem Zielserver ausgeführt werden können, um alle Mitgliedschaften in Serverrollen (z. B. sysadmin, securityadmin, processadmin) aus der Quellinstanz zu reproduzieren.

• Vereinfachung von Migrationen (z. B. bei Hardwarewechsel, Upgrade, oder Umzug in eine neue Umgebung), indem die manuelle Rekonstruktion von Rollenzuweisungen entfällt.

Verwendung

1. Quellserver (alter Server) – Skript ausführen.

2. Ausgabe – Die Spalte command enthält alle erforderlichen ALTER SERVER ROLE-Anweisungen.

3. Zielserver (neuer Server) – Die generierten Befehle ausführen (nachdem die entsprechenden Logins bereits auf dem Zielserver erstellt wurden).

4. Dokumentation – Die Ausführung protokollieren, um eine lückenlose Nachverfolgbarkeit zu gewährleisten.

Hinweise zur Ausführung auf dem Zielserver

• Die Befehle müssen von einem Login mit entsprechenden Rechten (mindestens ALTER ANY SERVER ROLE) ausgeführt werden.

• Falls ein Login oder eine Rolle nicht existiert, schlägt der Befehl fehl. Daher empfiehlt es sich, vorher die Logins zu synchronisieren (z. B. mit CREATE LOGIN ... aus einem separaten Skript).

• Die Änderungen sind sofort wirksam, kein Neustart erforderlich.

Wichtige Hinweise

• Das Skript erstellt keine Logins – diese müssen separat (z. B. mit CREATE LOGIN oder SSIS‑Migration) auf dem Zielserver vorhanden sein.

• Windows‑Gruppen (DOMAIN\Group) werden korrekt übernommen, systeminterne Konten (NT AUTHORITY\...) werden ausgefiltert.

• Falls die Mitgliedschaft auf dem Zielserver bereits besteht, schlägt der Befehl nicht fehl (keine doppelte Ausführung nötig).

Typische Einsatzszenarien

• Server‑Migration – Umzug einer Datenbankumgebung auf neue Hardware / neues Betriebssystem.

• Wiederherstellung – Nach einem Disaster Recovery, bei dem nur die Benutzerdatenbanken, aber nicht die master-Datenbank wiederhergestellt wurden.

• Umgebungssynchronisation – Abgleich von Entwicklungs‑, Test‑ und Produktionsservern hinsichtlich der Berechtigungsstruktur.

-- ======================================================================
-- Skript: Generiert ALTER SERVER ROLE ... ADD MEMBER Anweisungen
-- Zweck:  Migration von Serverrollen-Mitgliedschaften auf einen neuen Server
-- Verwendung:
--    1. Auf dem Quell-Server ausführen.
--    2. Die Ausgabe (Spalte 'command') auf dem Ziel-Server ausführen.
--    3. Dokumentation der ausgeführten Befehle.
-- Hinweis: Die Logins müssen auf dem Ziel-Server bereits existieren.
-- ======================================================================

SET NOCOUNT ON;

SELECT 
    R.name AS server_role,
    P.name AS role_member,
    -- Moderner Befehl mit QUOTENAME für sichere Behandlung von Sonderzeichen
    'ALTER SERVER ROLE ' + QUOTENAME(R.name) + ' ADD MEMBER ' + QUOTENAME(P.name) + ';' AS command
FROM sys.server_role_members RM
INNER JOIN sys.server_principals P 
    ON RM.member_principal_id = P.principal_id
INNER JOIN (
    SELECT principal_id, name 
    FROM sys.server_principals 
    WHERE type_desc = 'SERVER_ROLE'
) R ON RM.role_principal_id = R.principal_id
WHERE 
    -- 1. Temporäre Logins ausschließen (beginnen mit '#')
    P.name NOT LIKE '#%'
    -- 2. Windows-Systemkonten (NT AUTHORITY, NT SERVICE) ausschließen
    AND P.name NOT LIKE 'NT %'
    -- 3. Verhindern, dass Rollen Mitglied anderer Rollen sind (selten, aber möglich)
    AND P.type_desc <> 'SERVER_ROLE'
    -- 4. Eingebaute 'sa' ausschließen (existiert immer)
    AND P.name NOT IN ('sa')
    -- 5. (Optional) System-Logins wie ##MS_...## ausschließen – aktivieren bei Bedarf
    -- AND P.name NOT LIKE '##%##'
ORDER BY R.name, P.name;