ログインと権限の監査: 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 が必要。読み取り専用クエリのため安全。