로그인 및 권한 감사: 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가 필요하다. 읽기 전용 쿼리이며 안전함.