ログオン トリガー

適用対象: SQL Server Azure SQL Managed Instance

ログオン トリガーは、LOGON イベントに応答してストアド プロシージャを起動します。 このイベントは、SQL Server インスタンスでユーザー セッションが確立されるときに発生します。 ログオン トリガーは、ログインの認証段階が終了した後、ユーザー セッションが確立される前に発生します。 したがって、通常は、エラー メッセージや PRINT ステートメントからのメッセージはユーザーに表示されますが、このトリガー内で発生したすべてのメッセージは SQL Server のエラー ログに記録されます。 認証に失敗した場合、ログオン トリガーは起動しません。

ログオン トリガーを使用すると、ログインの利用状況を追跡したり、SQL Server へのログインを制限したり、特定のログインのセッション数を制限したりすることで、サーバー セッションを監査し制御できます。 たとえば、次のコードでは、ログイン login_test によってユーザー セッションが既に 3 つ生成されている場合、ログオン トリガーは、そのログインが開始する SQL Server へのログイン試行を拒否します。

USE master;
GO

CREATE LOGIN login_test
WITH PASSWORD = N'3KHJ6dhx(0xVYsdf' MUST_CHANGE,
CHECK_EXPIRATION = ON;
GO

GRANT VIEW SERVER STATE TO login_test;
GO

CREATE TRIGGER connection_limit_trigger ON ALL SERVER
WITH EXECUTE AS N'login_test'
FOR LOGON AS BEGIN
    IF ORIGINAL_LOGIN() = N'login_test'
    AND (
        SELECT COUNT(*)
        FROM sys.dm_exec_sessions
        WHERE is_user_process = 1
            AND original_login_name = N'login_test') > 3
    ROLLBACK;
END;

LOGON イベントは AUDIT_LOGIN SQL トレース イベントに対応しており、イベント通知で使用できます。 トリガーとイベント通知の主な相違点は、トリガーがイベントと同期的に発生するのに対し、イベント通知は非同期的に発生することです。 つまり、セッションが確立されないようにする場合は、ログオン トリガーを使用する必要があります。 この目的では、AUDIT_LOGIN イベントのイベント通知を使用できません。

最初と最後のトリガーの指定

LOGON イベントでは、複数のトリガーを定義できます。 sp_settriggerorder システム ストアド プロシージャを使用すると、定義したトリガーの 1 つを、イベントで起動される最初または最後のトリガーとして指定できます。 SQL Server では、残りのトリガーの実行順序は保証されません。

トランザクションを管理する

SQL Server によってログオン トリガーが起動される前に、SQL Server では、任意のユーザー トランザクションに依存しない暗黙のトランザクションが作成されます。 その結果、最初のログオン トリガーが起動し始めると、トランザクション数は 1 になります。 すべてのログオン トリガーの実行が完了すると、トランザクションがコミットされます。 SQL Server では、他の種類のトリガーと同様に、トランザクション数が 0 の状態でログオン トリガーの実行が完了すると、エラーが返されます。 ROLLBACK TRANSACTION ステートメントは、入れ子になったトランザクション内で実行される場合でも、トランザクション数を 0 にリセットします。 COMMIT TRANSACTION を実行すると、トランザクション数が 0 まで減少することがあります。 そのため、ログオン トリガー内では COMMIT TRANSACTION ステートメントを実行しないことをお勧めします。

ログオン トリガー内で ROLLBACK TRANSACTION ステートメントを使用する場合は、次の点を考慮してください。

  • ROLLBACK TRANSACTION が実行されるまでに行われたデータ変更がすべてロールバックされます。 これには、現在のトリガーによる変更、および同じイベントで先に実行されたトリガーによる変更が含まれます。 特定のイベントに対する残りのトリガーは実行されません。

  • 現在のトリガーは、ROLLBACK ステートメントの後にある残りのステートメントすべてを引き続き実行します。 これらのステートメントのいずれかがデータを変更する場合、その変更はロールバックされません。

LOGON イベントでトリガーを実行中に次のいずれかの状況が発生すると、ユーザー セッションは確立されません。

  • 元の暗黙のトランザクションがロールバックされるか失敗した。
  • 重大度が 20 を超えるエラーがトリガー内部で発生した。

ログオン トリガーを無効にする

ログオン トリガーを使用すると、 データベース エンジン sysadmin 固定サーバー ロールのメンバーを含むすべてのユーザーの への接続を効率的に禁止できます。 ログオン トリガーによって接続が禁止されているときでも、 sysadmin 固定サーバー ロールのメンバーは、専用管理者接続を使用するか、または データベース エンジン を最小構成モード (-f) で起動することにより、接続できます。 詳細については、「データベース エンジン サービスのスタートアップ オプション」を参照してください。