ログオントリガー

[アーティクル]
04/11/2024

適用対象: 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 を超えるエラーがトリガー内部で発生した。