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

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

データベース ユーザーがマスター データベースのログインに基づくが、ログインがマスターに存在しなくなったとき、SQL Server の孤立したユーザーが発生します。 これはログインが削除されたか、データベースが別のサーバーに移され、ログインがなくなったときに発生します。 このトピックでは、孤立ユーザーを見つけ、ログインに再マッピングする方法について説明します。

バックグラウンド

ログインに基づくセキュリティ プリンシパルを利用して SQL Server のインスタンスでデータベースに接続するには、 マスター データベースでプリンシパルに有効なログインを与える必要があります。 このログインは、プリンシパル ID を確認し、プリンシパルが SQL Serverのインスタンスに接続できるかどうかを決定する認証プロセスで使用されます。 サーバー インスタンスの SQL Server ログインは、 sys.server_principals カタログ ビューと sys.sql_logins 互換性ビューで表示できます。

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

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

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

• guest アカウント

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

• Microsoft Windows グループのメンバー

  Windows ユーザーから作成した SQL Server ログインは、Windows ユーザーが属しているグループがデータベースのユーザーである場合にデータベースにアクセスできます。

データベース ユーザーへの 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) が出力されます。

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 テーブルのユーザー SID の中に、マスター データベースの sql_logins テーブルのログイン SID と一致しないものがあるかどうかを調べます。

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

マスター データベースで、SID オプションで CREATE LOGIN ステートメントを使用して、なくなったログインを再作成します。前のセクションで取得したデータベース ユーザーの SID を提供します。

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

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

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

なくなったログインを再作成するとき、提供されたパスワードを使用し、データベースにアクセスできます。 それから、ALTER LOGIN ステートメントを使用し、ログイン アカウントのパスワードを変更できます。

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

参照

CREATE LOGIN (Transact-SQL)
ALTER USER (Transact-SQL)
CREATE USER (Transact-SQL)
sys.database_principals (Transact-SQL)
sys.server_principals (Transact-SQL)
sp_change_users_login (Transact-SQL)
sp_addlogin (Transact-SQL)
sp_grantlogin (Transact-SQL)
sp_password (Transact-SQL)
sys.sysusers (Transact-SQL)
sys.sql_loginssys.syslogins (Transact-SQL)