-- ===================================================================================================================
-- Collector: PermissionCheck (forbindelsestest under Servere og databaser, fra 1.3)
-- ===================================================================================================================
-- Hvad:        Forbindelsestesten: hvilket login agenten ankommer som, om det er sysadmin, og hvilke af rettighederne fra
--              db/permissions/grant-least-privilege.sql det har, som login'et selv ser dem (HAS_PERMS_BY_NAME). Før SQL
--              Server 2014 også de online brugerdatabaser, login'et ikke kan åbne. Resultatet vises med "Har", "Mangler"
--              eller "Valgfri, mangler", og GRANT-scriptet udfyldes med login'et.
-- Fil:         src/SQLInsight.Collectors/Discovery/PermissionCheck.cs
-- Interval:    Kun når der bedes om en forbindelsestest (en server tilføjes eller rettes, eller "Test forbindelsen")
-- Versioner:   Login og sysadmin: SQL Server 2000 og nyere. Rettighederne: SQL Server 2005 og nyere (SQL Server 2000 har
--              ikke HAS_PERMS_BY_NAME; dér vises en note i stedet).
--
-- Rettigheder til agentens login: ingen ud over at kunne logge ind. HAS_PERMS_BY_NAME og SUSER_SNAME svarer på
--   login'ets egne rettigheder. Det er netop de rettigheder, der tjekkes for:
--   GRANT VIEW SERVER STATE TO [agent];                -- før 2022 (fra 2022: VIEW SERVER PERFORMANCE STATE og VIEW SERVER SECURITY STATE)
--   GRANT VIEW ANY DEFINITION TO [agent];
--   GRANT CONNECT ANY DATABASE TO [agent];             -- SQL Server 2014+
--   GRANT SELECT ON msdb.dbo.backupset TO [agent];     -- osv. for backup-, restore- og jobtabellerne i msdb
--   GRANT ALTER TRACE TO [agent];                      -- valgfri
--
-- Bruges af:   Ingen regel. Kun visningen under Servere og databaser og API'et (GET …/connection-test).
-- Read-only:   Ja: kun SELECT af funktioner. Intet gives, ændres eller oprettes; GRANT-scriptet køres aldrig af SQLInsight.
-- Kørsel:     Kør i SSMS som agentens login. Hver variant er sin egen batch (GO); en variant for en anden SQL Server-version
--             end din kan fejle - det er forventet. Agenten kører altid med sessionsindstillingerne i _collectors/InstanceProbe.sql.
-- ===================================================================================================================

-- Variant: Alle versioner: login'et og om det er sysadmin
-- Kilde: PermissionCheck.WhoSql
-- Kode:  src/SQLInsight.Collectors/Discovery/PermissionCheck.cs : PermissionCheck.WhoSql
SELECT SUSER_SNAME(), IS_SRVROLEMEMBER('sysadmin')
GO

-- Variant: SQL Server 2022 og nyere: rettighederne
-- Kilde: PermissionCheck.BuildSql(2022+)
-- Kode:  src/SQLInsight.Collectors/Discovery/PermissionCheck.cs : PermissionCheck.BuildSql(checks)
SELECT 0, HAS_PERMS_BY_NAME(NULL, NULL, N'VIEW SERVER PERFORMANCE STATE')
UNION ALL SELECT 1, HAS_PERMS_BY_NAME(NULL, NULL, N'VIEW SERVER SECURITY STATE')
UNION ALL SELECT 2, HAS_PERMS_BY_NAME(NULL, NULL, N'VIEW ANY DEFINITION')
UNION ALL SELECT 3, HAS_PERMS_BY_NAME(NULL, NULL, N'CONNECT ANY DATABASE')
UNION ALL SELECT 4, HAS_PERMS_BY_NAME(N'msdb.dbo.backupset', N'OBJECT', N'SELECT')
UNION ALL SELECT 5, HAS_PERMS_BY_NAME(N'msdb.dbo.backupmediafamily', N'OBJECT', N'SELECT')
UNION ALL SELECT 6, HAS_PERMS_BY_NAME(N'msdb.dbo.backupmediaset', N'OBJECT', N'SELECT')
UNION ALL SELECT 7, HAS_PERMS_BY_NAME(N'msdb.dbo.restorehistory', N'OBJECT', N'SELECT')
UNION ALL SELECT 8, HAS_PERMS_BY_NAME(N'msdb.dbo.suspect_pages', N'OBJECT', N'SELECT')
UNION ALL SELECT 9, HAS_PERMS_BY_NAME(N'msdb.dbo.sysjobs', N'OBJECT', N'SELECT')
UNION ALL SELECT 10, HAS_PERMS_BY_NAME(N'msdb.dbo.sysjobsteps', N'OBJECT', N'SELECT')
UNION ALL SELECT 11, HAS_PERMS_BY_NAME(N'msdb.dbo.sysjobhistory', N'OBJECT', N'SELECT')
UNION ALL SELECT 12, HAS_PERMS_BY_NAME(N'msdb.dbo.sysjobactivity', N'OBJECT', N'SELECT')
UNION ALL SELECT 13, HAS_PERMS_BY_NAME(N'msdb.dbo.sysjobschedules', N'OBJECT', N'SELECT')
UNION ALL SELECT 14, HAS_PERMS_BY_NAME(N'msdb.dbo.sysschedules', N'OBJECT', N'SELECT')
UNION ALL SELECT 15, HAS_PERMS_BY_NAME(N'msdb.dbo.syscategories', N'OBJECT', N'SELECT')
UNION ALL SELECT 16, HAS_PERMS_BY_NAME(NULL, NULL, N'ALTER TRACE')
GO

-- Variant: SQL Server 2014 – 2019 og Azure SQL Managed Instance: rettighederne
-- Kilde: PermissionCheck.BuildSql(2014)
-- Kode:  src/SQLInsight.Collectors/Discovery/PermissionCheck.cs : PermissionCheck.BuildSql(checks)
SELECT 0, HAS_PERMS_BY_NAME(NULL, NULL, N'VIEW SERVER STATE')
UNION ALL SELECT 1, HAS_PERMS_BY_NAME(NULL, NULL, N'VIEW ANY DEFINITION')
UNION ALL SELECT 2, HAS_PERMS_BY_NAME(NULL, NULL, N'CONNECT ANY DATABASE')
UNION ALL SELECT 3, HAS_PERMS_BY_NAME(N'msdb.dbo.backupset', N'OBJECT', N'SELECT')
UNION ALL SELECT 4, HAS_PERMS_BY_NAME(N'msdb.dbo.backupmediafamily', N'OBJECT', N'SELECT')
UNION ALL SELECT 5, HAS_PERMS_BY_NAME(N'msdb.dbo.backupmediaset', N'OBJECT', N'SELECT')
UNION ALL SELECT 6, HAS_PERMS_BY_NAME(N'msdb.dbo.restorehistory', N'OBJECT', N'SELECT')
UNION ALL SELECT 7, HAS_PERMS_BY_NAME(N'msdb.dbo.suspect_pages', N'OBJECT', N'SELECT')
UNION ALL SELECT 8, HAS_PERMS_BY_NAME(N'msdb.dbo.sysjobs', N'OBJECT', N'SELECT')
UNION ALL SELECT 9, HAS_PERMS_BY_NAME(N'msdb.dbo.sysjobsteps', N'OBJECT', N'SELECT')
UNION ALL SELECT 10, HAS_PERMS_BY_NAME(N'msdb.dbo.sysjobhistory', N'OBJECT', N'SELECT')
UNION ALL SELECT 11, HAS_PERMS_BY_NAME(N'msdb.dbo.sysjobactivity', N'OBJECT', N'SELECT')
UNION ALL SELECT 12, HAS_PERMS_BY_NAME(N'msdb.dbo.sysjobschedules', N'OBJECT', N'SELECT')
UNION ALL SELECT 13, HAS_PERMS_BY_NAME(N'msdb.dbo.sysschedules', N'OBJECT', N'SELECT')
UNION ALL SELECT 14, HAS_PERMS_BY_NAME(N'msdb.dbo.syscategories', N'OBJECT', N'SELECT')
UNION ALL SELECT 15, HAS_PERMS_BY_NAME(NULL, NULL, N'ALTER TRACE')
GO

-- Variant: SQL Server 2005 – 2012: rettighederne
-- Kilde: PermissionCheck.BuildSql(2005)
-- Kode:  src/SQLInsight.Collectors/Discovery/PermissionCheck.cs : PermissionCheck.BuildSql(checks)
SELECT 0, HAS_PERMS_BY_NAME(NULL, NULL, N'VIEW SERVER STATE')
UNION ALL SELECT 1, HAS_PERMS_BY_NAME(NULL, NULL, N'VIEW ANY DEFINITION')
UNION ALL SELECT 2, HAS_PERMS_BY_NAME(N'msdb.dbo.backupset', N'OBJECT', N'SELECT')
UNION ALL SELECT 3, HAS_PERMS_BY_NAME(N'msdb.dbo.backupmediafamily', N'OBJECT', N'SELECT')
UNION ALL SELECT 4, HAS_PERMS_BY_NAME(N'msdb.dbo.backupmediaset', N'OBJECT', N'SELECT')
UNION ALL SELECT 5, HAS_PERMS_BY_NAME(N'msdb.dbo.restorehistory', N'OBJECT', N'SELECT')
UNION ALL SELECT 6, HAS_PERMS_BY_NAME(N'msdb.dbo.suspect_pages', N'OBJECT', N'SELECT')
UNION ALL SELECT 7, HAS_PERMS_BY_NAME(N'msdb.dbo.sysjobs', N'OBJECT', N'SELECT')
UNION ALL SELECT 8, HAS_PERMS_BY_NAME(N'msdb.dbo.sysjobsteps', N'OBJECT', N'SELECT')
UNION ALL SELECT 9, HAS_PERMS_BY_NAME(N'msdb.dbo.sysjobhistory', N'OBJECT', N'SELECT')
UNION ALL SELECT 10, HAS_PERMS_BY_NAME(N'msdb.dbo.sysjobactivity', N'OBJECT', N'SELECT')
UNION ALL SELECT 11, HAS_PERMS_BY_NAME(N'msdb.dbo.sysjobschedules', N'OBJECT', N'SELECT')
UNION ALL SELECT 12, HAS_PERMS_BY_NAME(N'msdb.dbo.sysschedules', N'OBJECT', N'SELECT')
UNION ALL SELECT 13, HAS_PERMS_BY_NAME(N'msdb.dbo.syscategories', N'OBJECT', N'SELECT')
UNION ALL SELECT 14, HAS_PERMS_BY_NAME(NULL, NULL, N'ALTER TRACE')
GO

-- Variant: SQL Server 2005 – 2012: online brugerdatabaser, login'et ikke kan åbne
-- Kilde: PermissionCheck.DatabasesWithoutAccessSql
-- Kode:  src/SQLInsight.Collectors/Discovery/PermissionCheck.cs : PermissionCheck.DatabasesWithoutAccessSql
SELECT name FROM sys.databases
WHERE state = 0 AND database_id > 4 AND is_read_only = 0 AND source_database_id IS NULL AND HAS_DBACCESS(name) = 0
ORDER BY name
GO