SQL Serverにおける孤立ユーザーの管理：包括的ガイド

SQL Serverの孤立したユーザーを管理することは、データベースのセキュリティと管理にとって重要です。孤立したユーザーは、マスターデータベースに対応するログインが存在しない状態でデータベースユーザーが存在する場合に発生します。この不一致はアクセスを妨げ、潜在的なセキュリティの脆弱性を引き起こす可能性があります。この記事では、孤立したユーザーとは何か、それらを検出する方法、および解決するための方法について、更新された例やシナリオを含めて探っていきます。

背景

SQL Serverデータベースに接続するには、ユーザーはマスターデータベースに有効なログインを持っている必要があります。このログインはユーザーを認証し、権限を持つデータベースユーザーにマッピングされます。このマッピングはセキュリティ識別子（SID）を使用して維持され、データベースユーザーとサーバーログインが正しくリンクされていることを保証します。

ログイン-ユーザーのマッピングの例外は以下の通りです:

1. 包含されたデータベースユーザー: データベースレベルで認証し、サーバーログインに依存しないユーザー。これらのユーザーはデータベースの携帯性を向上させますが、各データベースごとに別々に再作成する必要があります。
2. ゲストアカウント: デフォルトで有効になっており、明示的なマッピングなしでユーザーにアクセスを許可しますが、一般的にセキュリティ上の理由から非推奨です。
3. Microsoft Windowsグループメンバーシップ: Windowsグループメンバーは、グループがユーザーとして追加された場合にデータベースにアクセスできます。

データベースユーザーが孤立する理由は以下の通りです:

• 対応するログインが削除された場合。
• 関連するログインなしでデータベースが移行または復元された場合。
• ユーザーとログイン間のSIDマッピングが間違っています。

孤立したユーザーとは何ですか？

孤立したユーザーは、次のときに発生します：

• データベースユーザーは存在しますが、対応するSQL Serverのmasterデータベース内のログインは存在しません。
• データベースユーザーのSIDとログインのSIDが一致していません。

これらのシナリオは認証と承認を妨げ、エラーやアクセス制限を引き起こす可能性があります。

孤立したユーザーの検出

SQL Serverの場合

孤立したユーザーを特定するために次のクエリを使用してください：

SELECT dp.type_desc, dp.sid, dp.name AS user_name  

FROM sys.database_principals AS dp  

LEFT JOIN sys.server_principals AS sp  

    ON dp.sid = sp.sid  

WHERE sp.sid IS NULL  

    AND dp.authentication_type_desc = 'INSTANCE';

Azure SQL DatabaseまたはSynapse Analyticsの場合

1. masterデータベース内のログインのSIDを取得します：

SELECT sid 

FROM sys.sql_logins 

WHERE type = 'S'; 

2. 対象データベース内のユーザーのSIDを取得します：

SELECT name, sid, principal_id

FROM sys.database_principals 

WHERE type = 'S' 

  AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')

  AND authentication_type_desc = 'INSTANCE';

3. リストを比較して不一致を特定します。

孤立したユーザーの解決

1. SIDを持たないログインを再作成する 

ログインが削除された場合：

CREATE LOGIN <login_name>   

WITH PASSWORD = '<use_a_strong_password_here>',  

SID = <SID>;

2. 孤立したユーザーを既存のログインにマッピングする 

ログインが存在しているがマッピングされていない場合：

ALTER USER <user_name> WITH LOGIN = <login_name>;

3. ユーザーのパスワードを変更する 

ログインを再作成した後、そのパスワードを更新します：

ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';

sp_FindOrphanedUserの使用

ストアドプロシージャsp_FindOrphanedUserは、孤立したユーザーを検出および解決するための強力なツールです。以下を提供します：

1. 孤立したユーザーのリスト。
2. 修復のための自動生成されたスクリプト。
3. 孤立したユーザーが所有するスキーマに関する詳細です。

パッケージコードは sp_FindOrphanedUser から取得します。

手順の実行

デフォルトの実行：

EXEC sp_FindOrphanedUser;

特定のデータベースの場合：

EXEC sp_FindOrphanedUser @DatabaseName = '<DatabaseName>';

孤立したユーザーシナリオの例

例 1: ログインなしのユーザー

シナリオ

ログインが削除され、データベースユーザーが孤立します。

• 孤児ユーザーを検出します：
  MS SQL

  EXEC sp_FindOrphanedUser;

• ログインを再作成します：
  MS SQL

   

  CREATE LOGIN [User1] WITH PASSWORD = 'SecurePassword123';

  USE [ExampleDB];

  ALTER USER [User1] WITH LOGIN = [User1];

例2: SID不一致

シナリオ

ログインSIDがユーザーSIDと一致しません。

• There’s no clear mismatch in the provided text. The tags `` and `` are consistently paired and nested, and the SQL code within appears syntactically correct (though its functionality depends on the existence of the stored procedure `sp_FindOrphanedUser`). The only potential issue is the whitespace and formatting, which is subjective and not a structural mismatch.
• SID マッピングを修正してください：
  MS SQL

   

  USE [ExampleDB];

  EXEC sp_change_users_login 'UPDATE_ONE', 'User2', 'User2';

例3：スキーマを所有する孤立ユーザー

シナリオ

孤立ユーザーがスキーマの削除を妨げている。

• スキーマ所有権の検出：
  MS SQL

   

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• 再割り当てスキーマ所有権：
  MS SQL

   

  USE [ExampleDB];

  ALTER AUTHORIZATION ON SCHEMA::[User3Schema] TO [dbo];

  DROP USER [User3];

ベストプラクティス

• 制限付きデータベースユーザーの使用: サーバーログインへの依存を避ける。
• ログインの同期: データベースの移行中は常にログインを移行または復元する。
• 定期的な監査: セキュリティを維持するために、孤立ユーザーを定期的にチェックする。

結論

孤立ユーザーの管理は、データベースのセキュリティと運用効率を確保します。sp_FindOrphanedUserのようなクエリやツールを使用することで、問題を迅速に特定し解決し、正当なユーザーのシームレスなアクセスを維持できます。制限付きユーザーや定期的な監査のようなベストプラクティスを採用することで、将来的にこのような問題を防ぐことができます。