次の方法で共有

孤立したユーザーのトラブルシューティング (SQL Server)

適用対象:SQL ServerAzure SQL データベースAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

データベース ユーザーが master データベース内のログインに基づいているが、ログインが masterに存在しなくなった場合、ユーザーは SQL Server で孤立します。 これは、ログインが削除されたとき、またはログインが存在しない別のサーバーにデータベースが移動されたときに発生する可能性があります。 この記事では、孤立したユーザーを検索し、ログインに再マップする方法について説明します。

注意

移動される可能性のあるデータベースには包含データベース ユーザーを利用し、孤立ユーザーの可能性を減らします。 詳細については、「包含データベースを使用してデータベースの可搬性を確保する」を参照してください。

バックグラウンド

ログインに基づいてセキュリティ プリンシパル (データベース ユーザー ID) を使用する SQL Server のインスタンス上のデータベースへの接続の場合、プリンシパルは master データベースに有効なログインを持っている必要があります。 このログインは、プリンシパルの ID を検証し、プリンシパルが SQL Server のインスタンスへの接続を許可されているかどうかを判断する認証プロセスで使用されます。 サーバー インスタンスの SQL Server ログインは、 sys.server_principals カタログ ビューと sys.sql_logins 互換性ビューで表示できます。

SQL Server ログインは、SQL Server ログインにマップされた "データベース ユーザー" として個々のデータベースにアクセスします。 このルールには次の 3 つの例外があります。

  • 包含データベース ユーザー

    包含データベース ユーザーはユーザー データベース レベルで認証され、ログインには関連付けられません。 データベースの移植性が高く、包含データベース ユーザーが孤立することはできないため、このモデルをお勧めします。 ただし、データベースごとに再作成する必要があります。 多くのデータベースがある環境では、このモデルは実用的でない可能性があります。

  • ゲスト アカウント

    データベースでこのアカウントを有効にすると、データベース ユーザーにマップされていない SQL Server ログインが ゲスト ユーザーとしてデータベースにアクセスできるようになります。 既定では、 guest アカウントは無効になっています。

  • Microsoft Windows グループ メンバーシップ

    Windows ユーザーがデータベース内のユーザーでもある Windows グループのメンバーである場合、Windows ユーザーから作成された SQL Server ログインはデータベースにアクセスできます。

データベース ユーザーへの SQL Server ログインのマッピングに関する情報は、データベースに格納されます。 これには、データベース ユーザーの名前と、対応する SQL Server ログインのセキュリティ識別子 (SID) が含まれます。 データベース内での承認には、このデータベース ユーザーの権限が適用されます。

対応する SQL Server ログインが定義されていないか、サーバー インスタンスで正しく定義されていないデータベース ユーザー (ログインに基づく) は、インスタンスにサインインできません。 このようなユーザーは、そのサーバー インスタンスのデータベースの 孤立ユーザー と呼ばれます。 孤立は、データベース ユーザーが、master データベースに存在しないログイン SIDにマップされている場合に発生する可能性があります。 データベースを復元した後や、 SQL Server の別のインスタンスにアタッチしたが、そこではログインが作成されていないときも、孤立状態になることがあります。 データベース ユーザーは、対応する SQL Server ログインが削除された場合にも孤立状態になることがあります。 ログインが再作成された場合でも、別の SIDがあるため、データベース ユーザーは孤立したままです。

孤立したユーザーを検出する

SQL Server および PDW の場合

不足している SQL Server 認証ログインに基づいて 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';

出力には、SQL Server 認証ユーザーと、SQL Server ログインにリンクされていない現在のデータベース内の対応する SID が一覧表示されます。

Azure SQL Database と Azure Synapse Analytics の場合

sys.server_principals テーブルは、SQL Database または Azure Synapse Analytics では使用できません。 次の手順を実行して、これらの環境で孤立したユーザーを特定します。

  1. master データベースに接続し、次のクエリを使用してログインの SID を選択します。

    SELECT sid
FROM sys.sql_logins
WHERE type = 'S';

  2. 次のクエリを使用して、ユーザー データベースに接続し、 sys.database_principals テーブル内のユーザーの 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. 2 つのリストを比較して、ユーザー データベース sys.database_principals テーブルに、 master データベース sql_logins テーブル内のログイン SID と一致しないユーザー SID があるかどうかを判断します。

孤立したユーザーを解決する

master データベースで、SID オプションと共に CREATE LOGIN ステートメントを使用して、不足しているログインを再作成します。 前のセクションで取得したデータベース ユーザーの SID を指定します。

CREATE LOGIN <login_name>
WITH PASSWORD = '<use_a_strong_password_here>',
SID = <SID>;

孤立したユーザーを、 masterに既に存在するログインにマップするには、ユーザー データベースで ALTER USER ステートメントを実行し、ログイン名を指定します。

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

不足しているログインを再作成すると、ユーザーは指定されたパスワードを使用してデータベースにアクセスできます。 ユーザーは、 ALTER LOGIN ステートメントを使用してログイン アカウントのパスワードを変更できます。

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

重要

いずれのログインでも、固有のパスワードを変更できます。 ALTER ANY LOGIN 権限を持つログインだけが、他のユーザーのログイン パスワードを変更できます。 ただし、 sysadmin ロール メンバーのパスワードを変更できるのは、 sysadmin ロールのメンバーだけです。

関連コンテンツ