ログオントリガー

2017-06-13

ログオントリガーは、LOGON イベントに応答してストアドプロシージャを起動します。このイベントは、SQL Server のインスタンスでユーザーセッションが確立されたときに発生します。ログインの認証フェーズが完了した後、ユーザーセッションが実際に確立される前に、ログオントリガーが発生します。そのため、エラーメッセージや PRINT ステートメントからのメッセージなど、通常はユーザーに到達するトリガー内から送信されるすべてのメッセージは、SQL Server エラーログに転送されます。認証に失敗した場合、ログオントリガーは起動しません。

ログオントリガーを使用すると、ログインアクティビティの追跡、SQL Server へのログインの制限、特定のログインのセッション数の制限など、サーバーセッションを監査および制御できます。たとえば、次のコードでは、ログインによって作成されたユーザーセッションが既に 3 つある場合、ログオントリガーはログイン login_test によって開始された SQL Server へのログイン試行を拒否します。

USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = '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 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3
    ROLLBACK;
END;

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

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

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

トランザクションの管理

SQL Server がログオントリガーを起動する前に、SQL Server は、ユーザートランザクションとは独立した暗黙的なトランザクションを作成します。そのため、最初のログオントリガーの起動が開始されると、トランザクション数は 1 になります。すべてのログオントリガーの実行が完了すると、トランザクションがコミットされます。他の種類のトリガーと同様に、ログオントリガーがトランザクション数 0 で実行を終了すると、SQL Server はエラーを返します。 ROLLBACK TRANSACTION ステートメントは、入れ子になったトランザクション内でステートメントが発行された場合でも、トランザクション数を 0 にリセットします。 COMMIT TRANSACTION は、トランザクション数を 0 に減らします。そのため、ログオントリガー内で COMMIT TRANSACTION ステートメントを発行することはお勧めできません。

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

ROLLBACK TRANSACTION の時点まで行われたデータ変更はすべてロールバックされます。これらの変更には、現在のトリガーによって行われた変更と、同じイベントで実行された以前のトリガーによって行われた変更が含まれます。特定のイベントの残りのトリガーは実行されません。
現在のトリガーは、ROLLBACK ステートメントの後に表示される残りのステートメントを引き続き実行します。これらのステートメントのいずれかがデータを変更する場合、変更はロールバックされません。

LOGON イベントのトリガーの実行中に次のいずれかの条件が発生した場合、ユーザーセッションは確立されません。

元の暗黙的なトランザクションがロールバックされるか、失敗します。
重大度が 20 を超えるエラーは、トリガー本体内で発生します。