Аудит логинов и разрешений: члены sysadmin, рискованные разрешения, слабые пароли

Команда

-- 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;

Что делает

Выводит членство в серверных ролях, рискованные разрешения, такие как CONTROL SERVER/IMPERSONATE, SQL-логины с выключенной политикой паролей, заблокированные или с паролем, равным имени логина либо пустым; в базе — членство в ролях и явные разрешения на несистемные объекты.

Когда использовать

Для периодических ревизий доступа, перед аудитом, после ухода сотрудника из команды или при подозрении на инцидент безопасности.

Внимание

Разрешения, выданные через группы Windows, отображаются под именем группы; чтобы увидеть людей, используйте EXEC xp_logininfo N'DOMAIN\Group', 'members';. Перед отзывом чего-либо проверьте зависимости приложений. PWDCOMPARE работает только для SQL-логинов и требует CONTROL SERVER. Запрос только на чтение; безопасно.