Share via

How to Create a SQL Server Logon Trigger

  • Article

Why It is Important

If someone has accessed your SQL Server database directly, you need to know about it immediately. Database administrators who gain access can perform illicit actions; for instance, they can drop tables or create triggers without your notice. Native tools enable you to create a SQL Server logon trigger to audit access events on all servers and then keep an eye on logon events and trigger execution using SQL Server Management Studio (SSMS). But be ready to spend significant time creating a trigger code in Transact-SQL and then painstakingly poring through audit information to spot aberrant access. 

Native Audit

  1. Run SQL Server Management Studio and execute the following code in Transact-SQL:
CREATE DATABASE  LogonAudit /* Creates db for storing audit data */
USE [LogonAudit]
CREATE TABLE  LogonAuditing /* Creates table for  logons inside db */
(
    SessionId int,
    LogonTime datetime,
    HostName varchar(50),
    ProgramName varchar(500),
    LoginName varchar(50),
    ClientHost varchar(50)
)
GO
CREATE TRIGGER  [LogonAuditTrigger] /* Creates trigger for  logons */
ON ALL SERVER  
FOR LOGON
AS
  
BEGIN
DECLARE @LogonTriggerData xml,
@EventTime datetime,
@LoginName varchar(50),
@ClientHost varchar(50),
@LoginType varchar(50),
@HostName varchar(50),
@AppName varchar(500)
  
SET @LogonTriggerData = eventdata()
  
SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
SET @HostName = HOST_NAME()
SET @AppName = APP_NAME()
  
INSERT INTO  [LogonAudit].[dbo].[LogonAuditing]
(
SessionId,
LogonTime,
HostName,
ProgramName,
LoginName,
ClientHost
)
SELECT
@@spid,
@EventTime,
@HostName,
@AppName,
@LoginName,
@ClientHost
  
END
GO
  1. In order to see the collected information about logins in SQL Server Management Studio, run the following script:
SELECT *
  FROM [LogonAudit].[dbo].[LogonAuditing]
 You can save data to  CSV format after  that.
  1. Report example:
    http://img.netwrix.com/howtos/sql_server_logons_native.png

Credits

Originally posted - https://www.netwrix.com/how_to_create_sql_server_logon_trigger.html