Systems Engineering and RDBMS

Logon Triggers in SQL Server 2005 SP2

Posted by decipherinfosys on May 2, 2007

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:

auditlogin.jpg

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.

6 Responses to “Logon Triggers in SQL Server 2005 SP2”

  1. […] also included in BOL if you have the latest one installed. We had covered one of the new features logon triggers in one of our blog posts before. Another interesting new addition is the new storage format – […]

  2. […] introduced in service pack 2 (SP2) of MS SQLServer 2005, namely VARDECIMAL (new data type) and log-on trigger. In this blog post, we will cover yet another enhancement available in […]

  3. […] If you have server level triggers on the source instance, then you need to make sure that those are moved as well to the other […]

  4. […] This classification function executes post the login authentication and post the firing off any logon triggers.  BOL specifics certain considerations for creating the classifier function – you can read more on […]

  5. […] login auditing through configuration.  We had discussed logon triggers before in one of our posts here … do note that this functionality is available only from SQL Server 2005 SP2 onwards.  In […]

  6. […] Triggers and EventData() function – here, here and […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: