sqlinsight-pro/db/permissions/grant-least-privilege.sql

/*
    SQLInsight Pro: least-privilege rettigheder til agentens login
    ================================================================

    Giver agenten præcis de rettigheder, den skal bruge for at overvåge instansen. Aldrig sysadmin.
    Scriptet kan køres igen uden skade, fx efter en opgradering eller når der er oprettet nye databaser.

    Understøtter SQL Server 2005 og nyere. SQL Server 2000 har ikke VIEW SERVER STATE: se docs/legacy-versions.md.

    Forudsætning: login'et findes allerede, fx
        CREATE LOGIN [DOMÆNE\svc-sqlinsight$] FROM WINDOWS;   -- gMSA (anbefalet)
        CREATE LOGIN [sqlinsight-agent] FROM EXTERNAL PROVIDER; -- Azure SQL Managed Instance (Entra ID)

    Hvad der gives, og hvorfor:
      Server
        2005-2019: VIEW SERVER STATE                    DMV'er: waits, sessions, plan cache, I/O
        2022+:     VIEW SERVER PERFORMANCE STATE        samme, mere granulært
                   VIEW SERVER SECURITY STATE           sikkerheds-DMV'er (fx audit, certifikater)
        Alle:      VIEW ANY DEFINITION                  metadata (indexes, statistikker). Giver IKKE adgang til data.
        2014+:     CONNECT ANY DATABASE                 kan åbne alle databaser (nuværende og fremtidige). Giver IKKE adgang til data.
      Databaser (kun før 2014): en bruger uden rettigheder i hver online database, så DMV'er og Query Store kan læses.
      msdb:        SELECT på backup-, restore-, job- og suspect_pages-tabeller. Intet andet.

    Hvad der IKKE gives: adgang til tabeldata, skriverettigheder, xp_cmdshell eller sysadmin.
*/
SET NOCOUNT ON;

DECLARE @LoginName sysname;
SET @LoginName = N'DOMÆNE\svc-sqlinsight$';   -- <== ret til agentens login

DECLARE @Major int;
DECLARE @Sql nvarchar(max);
DECLARE @Quoted nvarchar(300);
SET @Major = CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(128)), 4) AS int);
/* Azure SQL Managed Instance reports 12.x but runs the newest engine. Use the broad, universally supported permissions. */
IF CAST(SERVERPROPERTY('EngineEdition') AS int) = 8 SET @Major = 15;
SET @Quoted = QUOTENAME(@LoginName);

IF @Major < 9
BEGIN
    RAISERROR(N'SQL Server 2000 understøttes ikke af dette script. Se docs/legacy-versions.md.', 16, 1);
    RETURN;
END;

IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @LoginName)
BEGIN
    RAISERROR(N'Login %s findes ikke. Opret det først.', 16, 1, @LoginName);
    RETURN;
END;

IF IS_SRVROLEMEMBER(N'sysadmin', @LoginName) = 1
    PRINT N'ADVARSEL: ' + @LoginName + N' er sysadmin. Agenten har ikke brug for det; fjern rollen.';

/* Server-rettigheder */
IF @Major >= 16
    SET @Sql = N'GRANT VIEW SERVER PERFORMANCE STATE TO ' + @Quoted + N'; GRANT VIEW SERVER SECURITY STATE TO ' + @Quoted + N';';
ELSE
    SET @Sql = N'GRANT VIEW SERVER STATE TO ' + @Quoted + N';';
EXEC (@Sql);

SET @Sql = N'GRANT VIEW ANY DEFINITION TO ' + @Quoted + N';';
EXEC (@Sql);

IF @Major >= 12
BEGIN
    SET @Sql = N'GRANT CONNECT ANY DATABASE TO ' + @Quoted + N';';
    EXEC (@Sql);
END
ELSE
BEGIN
    /* Før 2014: en bruger uden rettigheder i hver online database. Kør scriptet igen, når der oprettes nye. */
    DECLARE @Db sysname;
    DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT name FROM sys.databases
        WHERE state = 0 AND name NOT IN (N'master', N'tempdb', N'model', N'msdb') AND is_read_only = 0;
    OPEN db_cursor;
    FETCH NEXT FROM db_cursor INTO @Db;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Sql = N'USE ' + QUOTENAME(@Db) + N';
            IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE sid = SUSER_SID(' + QUOTENAME(@LoginName, '''') + N'))
                CREATE USER ' + @Quoted + N' FOR LOGIN ' + @Quoted + N';';
        EXEC (@Sql);
        FETCH NEXT FROM db_cursor INTO @Db;
    END;
    CLOSE db_cursor;
    DEALLOCATE db_cursor;
END;

/* msdb: backup-historik, jobs og suspect pages */
SET @Sql = N'USE msdb;
    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE sid = SUSER_SID(' + QUOTENAME(@LoginName, '''') + N'))
        CREATE USER ' + @Quoted + N' FOR LOGIN ' + @Quoted + N';
    GRANT SELECT ON dbo.backupset TO ' + @Quoted + N';
    GRANT SELECT ON dbo.backupmediafamily TO ' + @Quoted + N';
    GRANT SELECT ON dbo.backupmediaset TO ' + @Quoted + N';
    GRANT SELECT ON dbo.restorehistory TO ' + @Quoted + N';
    GRANT SELECT ON dbo.suspect_pages TO ' + @Quoted + N';
    GRANT SELECT ON dbo.sysjobs TO ' + @Quoted + N';
    GRANT SELECT ON dbo.sysjobsteps TO ' + @Quoted + N';
    GRANT SELECT ON dbo.sysjobhistory TO ' + @Quoted + N';
    GRANT SELECT ON dbo.sysjobactivity TO ' + @Quoted + N';
    GRANT SELECT ON dbo.sysjobschedules TO ' + @Quoted + N';
    GRANT SELECT ON dbo.sysschedules TO ' + @Quoted + N';
    GRANT SELECT ON dbo.syscategories TO ' + @Quoted + N';';
EXEC (@Sql);

/*
    Valgfrit: hvem og hvornår ved ændringer af serverroller.
    Rettighedsrevisionen (sysadmin m.m.) virker uden. Med ALTER TRACE kan agenten desuden læse default trace og vise,
    HVEM der gav en ny rettighed og HVORNÅR. ALTER TRACE giver også adgang til at oprette traces, så det er et bevidst
    valg; fjern kommentaren for at give den:

    SET @Sql = N'GRANT ALTER TRACE TO ' + @Quoted + N';';
    EXEC (@Sql);
*/

PRINT N'Rettigheder givet til ' + @LoginName + N'.';