Logon Triggers in SQL Server 2005 SP2

Logon triggers have existed in Oracle for a very long time. We had covered one of the possible applications of such a trigger in a previous blog post. In SP2, SQL Server now has the logon trigger which can be used to enforce security and do easy audit trails for the logins occurring in the application. These triggers fire after the authentication phase is completed but prior to the user session being established. Let’s do a test run to see this functionality:

/*Let’s create a login first and then the trigger*/

create login AuditLogin with password = ‘AuditLoginPswd’
go
/*Create a very simple login trigger */
create trigger AuditLogin_Demo
/* server means instance level*/
on all server
with execute as self
/* We specify the logon event at this stage
— If there are more than one connections,
— Issue a rollback*/
for logon
as begin
IF ORIGINAL_LOGIN()= ‘AuditLogin’ AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = ‘AuditLogin’) > 1
ROLLBACK;

end
go

Now, when the second attempt is made for the login, you will get this error:

This error indicates that you cannot login but does not tell you why – within your trigger code, you can have audit logic in place to record the reason(s) for the failure and use that for reporting/auditing/compliance purposes. You can also write up stored procedures which do certain amount of work when a logon event occurs. Another thing to note is that the engine starts an implicit transaction before the firing of the logon trigger – this is independent of any user transactions. Thus, when the logon trigger fires, the trancount is set to 1. After the logon trigger finishes, the commit occurs – likewise, in the above code, upon an error, the rollback rolls back the implicit transaction and sets the trancount to 0.