How to Create a SQL Server Logon Trigger


Native Auditing vs. Netwrix Auditor for SQL

We never share your data. Privacy Policy
Native Auditing Netwrix Auditor for SQL Server
Steps
  1. Run SQL Managements Studioand 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

  2. In order to see the collected information about logons in the SQL Management Studio run the following script:

    SELECT *
      FROM [LogonAudit].[dbo].[LogonAuditing]
     You can save data to CSV format after that.

Report sample: 

  1. Run Netwrix Auditor, Navigate to Reports → Choose “SQL Server” → “All SQL Server Logons” →Click  “Subscribe”.  
  2. Define recipient and filters, save subscription. You can receive it via e-mail or have it delivered to a specified shared folder according to the schedule you set. 

Report sample:

Surpass Creating Logon Triggers on SQL Server to Gain Control over Access

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. 


Netwrix Auditor for SQL Server eliminates the need to create any DDL triggers, including logon triggers in SQL Server, to enable auditing of your SQL Server. The solution helps you gain pervasive visibility into who attempted to access any of your SQL Server instances by showing the original logon attempt, when and where it was made, who made it, and whether it was successful or failed. With out-of-the-box insightful reports and Google-like search, you can identify and chase down emerging threats in no time. Plus, the solution stores all your logs for years in a cost-effective two-tiered storage (SQL database + file-based) and enables you to easily access them at any time. 
 

Join the discussion