Bezpečnostný baseline inštancie

/* DeadlockCafe | Bezpečnostný baseline inštancie
   Účel:  Jeden prehľad „kontrola → stav → odporúčanie“: sa, sysadmin, rizikové sp_configure voľby, TRUSTWORTHY, vlastníci databáz, password policy, prázdne heslá, guest a GRANT pre public.
   Režim: READ ONLY | SQL Server 2016 – 2022, on-premises
*/
SET NOCOUNT ON;

IF OBJECT_ID(N'tempdb..#chk') IS NOT NULL DROP TABLE #chk;
CREATE TABLE #chk
(
    id             int IDENTITY(1,1) PRIMARY KEY,
    priority       tinyint        NOT NULL,   -- 1 = riešiť, 2 = preveriť, 3 = info / OK
    area           nvarchar(40)   NOT NULL,
    check_name     nvarchar(200)  NOT NULL,
    object_name    nvarchar(400)  NULL,
    finding        nvarchar(4000) NOT NULL,
    recommendation nvarchar(400)  NULL
);

-- sa (SID 0x01) – zapnutý / premenovaný
INSERT INTO #chk (priority, area, check_name, object_name, finding, recommendation)
SELECT CASE WHEN p.is_disabled = 0 THEN 1 ELSE 3 END, N'Loginy', N'Účet sa (SID 0x01)', p.name,
       CASE WHEN p.is_disabled = 0 THEN N'zapnutý' ELSE N'vypnutý' END
       + CASE WHEN p.name = N'sa' THEN N', nepremenovaný' ELSE N', premenovaný' END,
       CASE WHEN p.is_disabled = 0 THEN N'Vypni (ALTER LOGIN ... DISABLE) a používaj menné účty; pred tým over joby a aplikácie.' ELSE N'OK' END
FROM sys.server_principals AS p
WHERE p.sid = 0x01;

-- členovia sysadmin
INSERT INTO #chk (priority, area, check_name, object_name, finding, recommendation)
SELECT CASE WHEN m.sid = 0x01 OR m.name LIKE N'NT SERVICE%' THEN 3 ELSE 2 END,
       N'Serverové role', N'Člen sysadmin', m.name,
       m.type_desc + CASE WHEN m.is_disabled = 1 THEN N' (vypnutý)' ELSE N'' END,
       N'Over, či je sysadmin naozaj potrebný; ľudia cez AD skupinu, aplikácie nikdy.'
FROM sys.server_role_members AS rm
JOIN sys.server_principals AS r ON r.principal_id = rm.role_principal_id
JOIN sys.server_principals AS m ON m.principal_id = rm.member_principal_id
WHERE r.name = N'sysadmin';

-- rizikové voľby sp_configure (clr strict security existuje od 2017)
INSERT INTO #chk (priority, area, check_name, object_name, finding, recommendation)
SELECT CASE WHEN CONVERT(int, c.value_in_use) <> x.expected THEN x.prio ELSE 3 END,
       N'Konfigurácia', c.name, NULL,
       N'value_in_use = ' + CONVERT(nvarchar(20), c.value_in_use)
       + CASE WHEN CONVERT(int, c.value) <> CONVERT(int, c.value_in_use)
              THEN N' (nastavené ' + CONVERT(nvarchar(20), c.value) + N', čaká na RECONFIGURE)' ELSE N'' END,
       CASE WHEN CONVERT(int, c.value_in_use) <> x.expected THEN x.advice ELSE N'OK' END
FROM sys.configurations AS c
JOIN (VALUES
        (N'xp_cmdshell',                 0, 1, N'Vypni; ak ho niečo potrebuje, nahraď SQL Agent krokom (CmdExec / PowerShell) s proxy.'),
        (N'clr enabled',                 0, 2, N'Nechaj zapnuté len pre známe CLR assembly; zisti, ktoré databázy ich používajú.'),
        (N'clr strict security',         1, 1, N'Nechaj 1 (predvolené od 2017); 0 umožní UNSAFE assembly bez podpisu.'),
        (N'Ole Automation Procedures',   0, 1, N'Vypni; sp_OA* procedúry spúšťajú COM objekty v procese SQL Servera.'),
        (N'Ad Hoc Distributed Queries',  0, 2, N'Vypni, ak nepoužívate OPENROWSET / OPENDATASOURCE; inak radšej linked server.'),
        (N'cross db ownership chaining', 0, 1, N'Vypni na úrovni servera; ak treba, zapni len pre konkrétne databázy.'),
        (N'remote admin connections',    0, 2, N'Vzdialený DAC je užitočný na clusteri a pri núdzi; inak ho CIS odporúča vypnúť. Rozhodni vedome.')
     ) AS x(name, expected, prio, advice) ON x.name = c.name;

-- TRUSTWORTHY ON (msdb ho má štandardne)
INSERT INTO #chk (priority, area, check_name, object_name, finding, recommendation)
SELECT 1, N'Databázy', N'TRUSTWORTHY ON', d.name,
       N'TRUSTWORTHY ON, vlastník ' + ISNULL(SUSER_SNAME(d.owner_sid), N'(SID bez loginu)'),
       N'Vypni (ALTER DATABASE ... SET TRUSTWORTHY OFF); pri vlastníkovi sysadmin umožňuje eskaláciu práv. CLR rieš podpisom.'
FROM sys.databases AS d
WHERE d.is_trustworthy_on = 1 AND d.name <> N'msdb';

-- vlastník databázy
INSERT INTO #chk (priority, area, check_name, object_name, finding, recommendation)
SELECT CASE WHEN sp.principal_id IS NULL OR sp.is_disabled = 1 THEN 1 ELSE 2 END,
       N'Databázy', N'Vlastník databázy nie je sa', d.name,
       N'vlastník ' + ISNULL(sp.name, N'(SID bez loginu)') + CASE WHEN sp.is_disabled = 1 THEN N' – login je vypnutý' ELSE N'' END,
       N'Zjednoť vlastníka (ALTER AUTHORIZATION ON DATABASE::[db] TO [sa] alebo dedikovaný login); osobný účet pri odchode zamestnanca robí problémy.'
FROM sys.databases AS d
LEFT JOIN sys.server_principals AS sp ON sp.sid = d.owner_sid
WHERE d.database_id > 4 AND d.owner_sid <> 0x01;

-- SQL loginy bez password policy / expirácie
INSERT INTO #chk (priority, area, check_name, object_name, finding, recommendation)
SELECT CASE WHEN l.is_policy_checked = 0 AND l.is_disabled = 0 THEN 1 ELSE 2 END,
       N'Loginy', N'SQL login bez password policy alebo expirácie', l.name,
       N'is_policy_checked = ' + CONVERT(nvarchar(1), l.is_policy_checked)
       + N', is_expiration_checked = ' + CONVERT(nvarchar(1), l.is_expiration_checked)
       + CASE WHEN l.is_disabled = 1 THEN N' (vypnutý)' ELSE N'' END,
       N'Zapni CHECK_POLICY; pre ľudí aj CHECK_EXPIRATION. Servisné účty rieš rotáciou hesla.'
FROM sys.sql_logins AS l
WHERE (l.is_policy_checked = 0 OR l.is_expiration_checked = 0)
  AND l.name NOT LIKE N'##%';

-- prázdne heslo alebo heslo = meno loginu (len áno / nie)
INSERT INTO #chk (priority, area, check_name, object_name, finding, recommendation)
SELECT 1, N'Loginy', N'Slabé heslo', l.name,
       CASE WHEN PWDCOMPARE(N'', l.password_hash) = 1 THEN N'prázdne heslo' ELSE N'heslo sa zhoduje s menom loginu' END
       + CASE WHEN l.is_disabled = 1 THEN N' (login je vypnutý)' ELSE N'' END,
       N'Okamžite zmeň heslo a zapni CHECK_POLICY.'
FROM sys.sql_logins AS l
WHERE l.password_hash IS NOT NULL
  AND (PWDCOMPARE(N'', l.password_hash) = 1 OR PWDCOMPARE(l.name, l.password_hash) = 1);

IF EXISTS (SELECT 1 FROM sys.sql_logins WHERE password_hash IS NULL AND name NOT LIKE N'##%')
    INSERT INTO #chk (priority, area, check_name, object_name, finding, recommendation)
    SELECT 2, N'Loginy', N'Slabé heslo', NULL,
           N'Kontrola nevykonaná pre ' + CONVERT(nvarchar(10), COUNT(*)) + N' loginov – chýba oprávnenie na password_hash.',
           N'Spusti pod CONTROL SERVER (2016–2019) alebo VIEW ANY CRYPTOGRAPHICALLY SECURED DEFINITION (2022).'
    FROM sys.sql_logins
    WHERE password_hash IS NULL AND name NOT LIKE N'##%';

-- guest a GRANT pre public v používateľských databázach
DECLARE @db sysname, @sql nvarchar(max);
DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases
    WHERE database_id > 4 AND state = 0 AND HAS_DBACCESS(name) = 1
    ORDER BY name;
OPEN dbs;
FETCH NEXT FROM dbs INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'USE ' + QUOTENAME(@db) + N';
INSERT INTO #chk (priority, area, check_name, object_name, finding, recommendation)
SELECT 1, N''Databázy'', N''guest má CONNECT'', DB_NAME(), N''používateľ guest je v databáze povolený'',
       N''REVOKE CONNECT FROM guest; – v používateľských databázach guest nemá mať prístup.''
FROM sys.database_permissions AS pe
WHERE pe.class = 0 AND pe.grantee_principal_id = DATABASE_PRINCIPAL_ID(N''guest'')
  AND pe.permission_name = N''CONNECT'' AND pe.state IN (''G'', ''W'');

INSERT INTO #chk (priority, area, check_name, object_name, finding, recommendation)
SELECT 2, N''Oprávnenia'', N''GRANT pre public na objekt'',
       DB_NAME() + N''.'' + ISNULL(OBJECT_SCHEMA_NAME(pe.major_id), N''?'') + N''.'' + o.name,
       pe.state_desc + N'' '' + pe.permission_name,
       N''Práva daj konkrétnej role alebo používateľovi, nie public.''
FROM sys.database_permissions AS pe
JOIN sys.objects AS o ON o.object_id = pe.major_id
WHERE pe.class = 1 AND pe.grantee_principal_id = 0
  AND pe.state IN (''G'', ''W'') AND o.is_ms_shipped = 0;';
    BEGIN TRY
        EXEC sys.sp_executesql @sql;
    END TRY
    BEGIN CATCH
        INSERT INTO #chk (priority, area, check_name, object_name, finding, recommendation)
        VALUES (2, N'Databázy', N'Databáza preskočená', @db, ERROR_MESSAGE(), N'Spusti s vyššími právami.');
    END CATCH;
    FETCH NEXT FROM dbs INTO @db;
END;
CLOSE dbs;
DEALLOCATE dbs;

SELECT CASE priority WHEN 1 THEN N'RIEŠIŤ' WHEN 2 THEN N'PREVERIŤ' ELSE N'INFO / OK' END AS priority_desc,
       area, check_name, object_name, finding, recommendation
FROM #chk
ORDER BY priority, area, check_name, object_name;