Audit des logins et permissions : membres sysadmin, permissions à risque, mots de passe faibles

Commande

-- 1) Server role memberships:
SELECT r.name AS server_role, m.name AS member, m.type_desc, m.is_disabled, m.create_date
FROM   sys.server_role_members rm
  JOIN sys.server_principals r ON r.principal_id = rm.role_principal_id
  JOIN sys.server_principals m ON m.principal_id = rm.member_principal_id
ORDER BY r.name, m.name;

-- 2) Risky server permissions:
SELECT pr.name AS grantee, pr.type_desc, pe.permission_name, pe.state_desc
FROM   sys.server_permissions pe
  JOIN sys.server_principals pr ON pr.principal_id = pe.grantee_principal_id
WHERE  pe.permission_name IN (N'CONTROL SERVER', N'IMPERSONATE ANY LOGIN', N'ALTER ANY LOGIN',
                              N'ALTER ANY SERVER ROLE', N'ALTER SETTINGS', N'IMPERSONATE')
   OR  pe.state_desc = N'GRANT_WITH_GRANT_OPTION';

-- 3) SQL login health (policy, lockout, password = login name):
SELECT sl.name, sl.is_disabled, sl.is_policy_checked, sl.is_expiration_checked,
       LOGINPROPERTY(sl.name, 'PasswordLastSetTime') AS pwd_last_set,
       LOGINPROPERTY(sl.name, 'IsLocked')            AS is_locked,
       LOGINPROPERTY(sl.name, 'BadPasswordCount')    AS bad_pwd_count,
       PWDCOMPARE(sl.name, sl.password_hash)         AS pwd_equals_name,
       PWDCOMPARE(N'', sl.password_hash)             AS pwd_empty
FROM   sys.sql_logins sl;

-- 4) Database role memberships and explicit object permissions:
USE [Musteri];
SELECT r.name AS db_role, m.name AS member, m.type_desc
FROM   sys.database_role_members rm
  JOIN sys.database_principals r ON r.principal_id = rm.role_principal_id
  JOIN sys.database_principals m ON m.principal_id = rm.member_principal_id
WHERE  m.name <> N'dbo'
ORDER BY r.name, m.name;

SELECT pr.name AS grantee, pe.class_desc, pe.permission_name, pe.state_desc,
       OBJECT_SCHEMA_NAME(pe.major_id) AS schema_name,
       CASE pe.class WHEN 1 THEN OBJECT_NAME(pe.major_id) WHEN 3 THEN SCHEMA_NAME(pe.major_id) END AS object_name
FROM   sys.database_permissions pe
  JOIN sys.database_principals pr ON pr.principal_id = pe.grantee_principal_id
WHERE  (pr.name NOT IN (N'public', N'dbo') AND NOT (pe.class = 0 AND pe.permission_name = N'CONNECT'))
   OR  (pr.name = N'public' AND OBJECT_SCHEMA_NAME(pe.major_id) <> N'sys')
ORDER BY pr.name, pe.class_desc;

Ce que fait la commande

Liste les appartenances aux rôles serveur, les permissions à risque comme CONTROL SERVER/IMPERSONATE, les logins SQL avec politique de mot de passe désactivée, verrouillés, ou dont le mot de passe est égal au nom du login ou vide ; dans la base, les appartenances aux rôles et les permissions explicites sur les objets non système.

Quand l'utiliser

Pour les revues d'accès périodiques, avant un audit, après le départ d'un membre de l'équipe, ou en cas de suspicion d'incident de sécurité.

Attention

Les permissions accordées via des groupes Windows apparaissent sous le nom du groupe ; pour voir les personnes, utilisez EXEC xp_logininfo N'DOMAIN\Group', 'members';. Vérifiez les dépendances applicatives avant de révoquer quoi que ce soit. PWDCOMPARE ne fonctionne que pour les logins SQL et nécessite CONTROL SERVER. Requête en lecture seule ; sûre.