Post
Ask Question

Logon Trigger in SQL Server 2012

This is the DDL trigger for security purposes as determined by company polices. Basically a logon trigger fires Stored Procedures in response to a LOGON event. When a user session is established with an instance of SQL Server the event is raised, that was introduced in SQL Server 2005 SP2.

It can be used for audit and control server sessions. You can also track the login activity, restricting logins to SQL Server, or limiting or denying the number of login sessions for a specific user. You can define more than one Logon trigger on the server.

It works when the user has access to the instance, then the trigger fires.

For example:

  1.  USE DBATest  
  2. GO  
  3.   
  4. -- Creating audit table for login details  
  5. CREATE TABLE Auditing  
  6. (  
  7. SessionId INT,  
  8. LogonTime DATETIME,  
  9. HostName VARCHAR(50),  
  10. ProgramName VARCHAR(500),  
  11. LoginName VARCHAR(50),  
  12. ClientHost VARCHAR(50))  
  13. GO  
  14. USE [MASTER]  
  15. GO  
  16. -- Creating Audit trigger for logon  
  17. CREATE TRIGGER Audit_TR  
  18. ON ALL SERVER WITH EXECUTE AS 'sa'  
  19. FOR LOGON  
  20. AS  
  21. BEGIN  
  22. DECLARE @LogonTRData XML,  
  23. @EventTime datetime,  
  24. @LoginName varchar(50),  
  25. @ClientHost varchar(50),  
  26. @LoginType varchar(50),  
  27. @HostName varchar(50),  
  28. @AppName varchar(500)  
  29. SET @LogonTRData = EVENTDATA()  
  30. SET @EventTime = @LogonTRData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')  
  31. SET @LoginName = @LogonTRData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')  
  32. SET @ClientHost = @LogonTRData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')  
  33. SET @HostName = HOST_NAME()  
  34. SET @AppName = APP_NAME()--,program_name()  
  35. INSERT INTO DBATest.dbo.Auditing  
  36. (  
  37. SessionId,  
  38. LogonTime,  
  39. HostName,  
  40. ProgramName,  
  41. LoginName,  
  42. ClientHost  
  43. )  
  44. SELECT  
  45. @@SPID,  
  46. @EventTime,  
  47. @HostName,  
  48. @AppName,  
  49. @LoginName,  
  50. @ClientHost  
  51. END  
  52. GO 

The EVENTDATA() is an XML document that is only available within the context of the DDL Trigger. It has the following schema:

  1.  <EVENT_INSTANCE>   
  2.   <EventType>event_type</EventType>   
  3.   <PostTime>post_time</PostTime>   
  4.   <SPID>spid</SPID>   
  5.   <ServerName>server_name</ServerName>   
  6.   <LoginName>login_name</LoginName>   
  7.   <LoginType>login_type</LoginType>   
  8.   <SID>sid</SID>   
  9.   <ClientHost>client_host</ClientHost>   
  10.   <IsPooled>is_pooled</IsPooled>   
  11. </EVENT_INSTANCE> 

In the following screen you can see the audit table details:

table

You can restrict a user from opening more than 5 connections with the server at the same time.

For example:

  1.  CREATE TRIGGER Connection_Limit_tr  
  2. ON ALL SERVER WITH EXECUTE AS 'sa'  
  3. FOR LOGON  
  4. AS  
  5. BEGIN  
  6. IF ORIGINAL_LOGIN() <> 'sa'  
  7. AND  
  8. ( SELECT COUNT(*)  
  9. FROM sys.dm_exec_sessions  
  10. WHERE Is_User_Process = 1 AND  
  11. Original_Login_Name = ORIGINAL_LOGIN()  
  12. ) > 1  
  13. ROLLBACK  
  14. END 

"When" limits the connections for all Logins except 'sa'. Once the connection limit is reached the user will not be able to create a new connection. Then an error message will be issued like this:

error message

You can place restrictions on a user outside of office hours.

For example:

  1.  CREATE TRIGGER Connnection_TimeLimit_TR  
  2. ON ALL SERVER WITH EXECUTE AS 'sa'  
  3. FOR LOGON  
  4. AS  
  5. BEGIN  
  6. IF ( (ORIGINAL_LOGIN() <> 'sa')  
  7. AND  
  8. (DATEPART(HOUR, GETDATE()) BETWEEN 16 AND 17)  
  9. )  
  10. ROLLBACK  
  11. END 

I have defined here the out-of-office hours are 8 AM to 6 PM.

SQl

If you want to connect during out of office hours, then the following error will be shown.

You can DROP a LOGON Trigger using the following script:

  1. DROP TRIGGER triggerName ON ALL SERVER 


Similar Articles