SQL Serverのログオントリガーを使う

SQL Serverではログオントリガーがあり、ユーザがログオンする度に実行されるトリガーを定義できます。
http://msdn.microsoft.com/ja-jp/library/bb326598.aspx

これを使うことでログオンの監査や同時セッション数の管理が可能です。
実際使う時に下手なトリガーを作ると、ログオン操作そのものが失敗してしまうことがあります。

2011-10-26 22:03:54.88 ログオン エラー: 17892、重大度: 20、状態: 1。
2011-10-26 22:03:54.88 ログオン Logon failed for login 'test' due to trigger execution. [クライアント: ]

このときログオントリガーを削除するユーザがログオンできず、トリガーの削除ができなくなってしまいます。
そこで、このときはsqlcmdから以下のようにして管理者権限を使って接続してトリガーを削除しましょう。

C:\> sqlcmd.exe -S admin:servername -U username -P password
1>drop trigger connection_trigger on ALL Server;
2>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;

BEGINからENDのブロックがあり、この中のステートメントが
結果セットを返さないように作成することです。
結果セットが返るとトリガー実行に失敗し、以下のエラーがエラーログに出力されます。

2011-10-26 22:33:32.18 spid52 エラー: 575、重大度: 16、状態: 1。
2011-10-26 22:33:32.18 spid52 LOGON トリガーから結果セットが返されました。結果セットを返さないように LOGON トリガーを変更してください。

スポンサードリンク