patternsqlMinor

Get SQL query in logon trigger

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

triggerlogonsqlqueryget

Problem

I have a requirement for a logon trigger. I need to capture the SQL query that is coming to SQL Server from an Excel Pivot file.

Currently I am using the following code:

CREATE TRIGGER [MyLogonTrigger] ON ALL SERVER FOR LOGON
AS 
BEGIN
    IF PROGRAM_NAME() <> 'Microsoft%'

    DECLARE @sqltext VARBINARY(128)
    SELECT @sqltext = sql_handle
    FROM sys.sysprocesses
    WHERE spid = @@spid

    DECLARE @SQLQuery varchar(MAX)
    set @SQLQuery = (select TEXT
    FROM sys.dm_exec_sql_text(@sqltext))

    INSERT INTO TestDatabase.dbo.LogonAudit 
             (
                    ... audit columns ...
             ) 
       Select
                    SUSER_SNAME()
                    ,GETDATE()
                    ,@@SPID
                    ,PROGRAM_NAME()
                    ,ORIGINAL_DB_NAME()
                    ,HOST_NAME()
                    ,client_net_address
                    ,@SQLQuery
   FROM sys.dm_exec_connections 
   WHERE session_id = @@SPID
END;

The problem I am facing is that @SQLQuery is the Create Trigger definition code every time, and not the actual query that is passed to SQL from the Excel file when the pivot is refreshed.

I think this has something to do with the fact that the login and the actual select query execution happens on two different transactions (SPID's).

Any idea how I can get the actual select query which excel uses to retrieve the data in a login trigger?

Solution

As Brent Ozar noted in his answer, you are attempting to use a Logon trigger for a purpose for which it was not designed. Logon triggers are typically used to either capture details about who is logging onto the server, or to deny particular combinations of users/machines etc from connecting.

Luckily, Extended Events offers a mechanism for capturing T-SQL in memory that, when configured correctly, can be fairly light on system resources.

I've used something like the following code in the past to capture T-SQL from a particular user or machine:

```
IF EXISTS
(
SELECT 1
FROM sys.server_event_sessions dxs
WHERE dxs.name = 'queries'
)
BEGIN
IF EXISTS (
SELECT 1
FROM sys.dm_xe_sessions dxs
WHERE dxs.name = 'queries'
)
BEGIN
ALTER EVENT SESSION queries
ON SERVER
STATE = STOP;
END
DROP EVENT SESSION queries
ON SERVER;
END

CREATE EVENT SESSION queries ON SERVER
ADD EVENT sqlserver.sql_statement_starting
(
ACTION
(
package0.collect_system_time
, package0.event_sequence / SQL Server 2012+ /
, sqlserver.client_app_name
, sqlserver.client_hostname
, sqlserver.database_name / SQL Server 2012+ /
, sqlserver.plan_handle
, sqlserver.sql_text
, sqlserver.username
, sqlserver.request_id
, sqlserver.session_id
)
WHERE sqlserver.username = N'some_user_name'
AND sqlserver.database_id = 6 / track a specific database only /
AND sqlserver.client_hostname <> 'excluded_host_name'
)
, ADD EVENT sqlserver.sql_statement_completed
(
ACTION
(
package0.collect_system_time
, package0.event_sequence / SQL Server 2012+ /
, sqlserver.client_app_name
, sqlserver.client_hostname
, sqlserver.database_name / SQL Server 2012+ /
, sqlserver.plan_handle
, sqlserver.sql_text
, sqlserver.username
, sqlserver.request_id
, sqlserver.session_id
)
WHERE sqlserver.username = N'some_user_name'
AND sqlserver.database_id = 6
AND sqlserver.client_hostname <> 'excluded_host_name'
)
, ADD EVENT sqlserver.error_reported
(
ACTION
(
package0.collect_system_time
, package0.event_sequence / SQL Server 2012+ /
, sqlserver.client_app_name
, sqlserver.client_hostname
, sqlserver.database_name / SQL Server 2012+ /
, sqlserver.plan_handle
, sqlserver.sql_text
, sqlserver.username
, sqlserver.request_id
, sqlserver.session_id
)
WHERE sqlserver.username = N'some_user_name'
AND sqlserver.database_id = 6
AND sqlserver.client_hostname <> 'excluded_host_name'
/ fluff errors below - for SQL Server 2008 R2, use "error" instead of "error_number" /
AND error_number <> 5703 / Changed language setting to %.ls. */
AND error_number <> 5701 / Changed database context to '%.ls'. */
AND error_number <> 2528 /* DBCC execution completed. If DBCC printed error messages, contact
your system administrator. */
AND error_number <> 7969 / No active open transactions. /
AND error_number <> 4035 / Processed %I64d pages for database '%ls', file '%ls' on file %d. /
AND error_number <> 18265/* Log was backed up. Database: %s, creation date(time): %s(%s),
first LSN: %s, last LSN: %s, number of dump devices: %d, device information: (%s).
This is an informational message only. No user action is required. */
AND error_number <> 3014 / %hs successfully processed %I64d pages in %d.%03d seconds (%d.%03d MB/sec). /
AND error_number <> 14570/ (Job outcome) /
AND error_number <> 8153 / Warning: Null value is eliminated by an aggregate or other SET operation. /
)
ADD TARGET package0.ring_buffer
(
SET max_memory = 1024
)
/ add or remove the below target as required. /
, ADD TARGET package0.asynchronous_file_target
(
SET filename = 'C:\temp\queries_xe_target.xel'
, max_file_size = 10 / max size in MB /
, max_rollover_files = 10
, increment = 1 / file growth increment in MB /
)
/ Don't start this Extended Events session automatically when the server starts /
WITH
(
STARTUP_STATE = OFF /* Extended Event Session will NOT be automatically
started at server startup */
, TRACK_CAUSALITY = ON
, MAX_MEMORY = 5MB / buffer size to use /
, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS / ALLOW_MULTIPLE_EVENT_LOSS or NO_EVENT_LOSS /
, MAX_DISPATCH_LATENCY = 15 SECONDS /* maximum number of seconds until buffer contents
are written to the t

Code Snippets

IF EXISTS 
(
    SELECT 1 
    FROM sys.server_event_sessions dxs 
    WHERE dxs.name = 'queries'
)
BEGIN
    IF EXISTS (
        SELECT 1 
        FROM sys.dm_xe_sessions dxs 
        WHERE dxs.name = 'queries'
    )
    BEGIN
        ALTER EVENT SESSION queries 
        ON SERVER 
        STATE = STOP;
    END
    DROP EVENT SESSION queries 
    ON SERVER;
END

CREATE EVENT SESSION queries ON SERVER 
ADD EVENT sqlserver.sql_statement_starting
(
    ACTION 
    (
        package0.collect_system_time
        , package0.event_sequence /* SQL Server 2012+ */
        , sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_name /* SQL Server 2012+ */
        , sqlserver.plan_handle
        , sqlserver.sql_text
        , sqlserver.username
        , sqlserver.request_id
        , sqlserver.session_id
    )
    WHERE sqlserver.username = N'some_user_name'
        AND sqlserver.database_id = 6 /* track a specific database only */
        AND sqlserver.client_hostname <> 'excluded_host_name'
) 
, ADD EVENT sqlserver.sql_statement_completed
(
    ACTION 
    (
        package0.collect_system_time
        , package0.event_sequence /* SQL Server 2012+ */
        , sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_name /* SQL Server 2012+ */
        , sqlserver.plan_handle
        , sqlserver.sql_text
        , sqlserver.username
        , sqlserver.request_id
        , sqlserver.session_id
    )
    WHERE sqlserver.username = N'some_user_name'
        AND sqlserver.database_id = 6 
        AND sqlserver.client_hostname <> 'excluded_host_name'
) 
, ADD EVENT sqlserver.error_reported 
(
    ACTION 
    (
        package0.collect_system_time
        , package0.event_sequence /* SQL Server 2012+ */
        , sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_name /* SQL Server 2012+ */
        , sqlserver.plan_handle
        , sqlserver.sql_text
        , sqlserver.username
        , sqlserver.request_id
        , sqlserver.session_id
    )
    WHERE sqlserver.username = N'some_user_name'
        AND sqlserver.database_id = 6 
        AND sqlserver.client_hostname <> 'excluded_host_name'
        /* fluff errors below - for SQL Server 2008 R2, use "error" instead of "error_number" */
        AND error_number <> 5703 /* Changed language setting to %.*ls. */
        AND error_number <> 5701 /* Changed database context to '%.*ls'. */
        AND error_number <> 2528 /* DBCC execution completed. If DBCC printed error messages, contact 
                            your system administrator. */
        AND error_number <> 7969 /* No active open transactions. */
        AND error_number <> 4035 /* Processed %I64d pages for database '%ls', file '%ls' on file %d. */
        AND error_number <> 18265/* Log was backed up. Database: %s, creation date(time): %s(%s), 
                first LSN: %s, last LSN: %s, number of dump devices: %d, device information:

IF EXISTS 
(
    SELECT 1 
    FROM sys.server_event_sessions dxs 
    WHERE dxs.name = 'queries'
)
BEGIN
    ALTER EVENT SESSION queries ON SERVER STATE = START;
END

SELECT SessionName = xe.name
    , TargetName = xet.target_name
    , EventData = CONVERT(xml, xet.target_data)
FROM sys.dm_xe_session_targets AS xet
    INNER JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
WHERE xe.name = 'queries';

DECLARE @xml XML;

SELECT TOP(1) @xml = CONVERT(xml, xet.target_data)
FROM sys.dm_xe_session_targets AS xet
    INNER JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
WHERE xe.name = 'queries'
    AND xet.target_name = 'ring_buffer';

IF OBJECT_ID('tempdb..#xmlResults') IS NOT NULL
DROP TABLE #xmlResults;

CREATE TABLE #xmlResults
(
    RowNum INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , xeXML XML NOT NULL
);

INSERT INTO #xmlResults (xeXML)
SELECT xm.s.query('.')
FROM @xml.nodes('/RingBufferTarget/event') AS xm(s)
OPTION (Optimize FOR (@xml = Null)); -- immensely improves performance in SQL Server 2008

SELECT t.EventName
    , DateStamp = DATEADD(HOUR, -6, t.EventDateStamp)
    , DatabaseName = d.name
    , t.ErrorNumber
    , t.ErrorSeverity
    , t.ErrorState
    , t.ErrorMessage
    , t.CollectSystemTime
    , t.ClientAppName
    , t.ClientHostName
    , t.PlanHandle
    , t.SqlText
    , t.UserName
FROM (
        SELECT EventName =          xeXML.value('(event/@name)[1]','varchar(500)')
            , EventDateStamp =      xeXML.value('(event/@timestamp)[1]','datetime')
            , DatabaseID =          xeXML.value('(event/data[(@name)[1] eq "source_database_id"]/value/text())[1]','varchar(255)')
            , ErrorNumber =         xeXML.value('(event/data[(@name)[1] eq "error"]/value/text())[1]','varchar(255)')
            , ErrorSeverity =       xeXML.value('(event/data[(@name)[1] eq "severity"]/value/text())[1]','varchar(255)')
            , ErrorState =          xeXML.value('(event/data[(@name)[1] eq "state"]/value/text())[1]','varchar(255)')
            , ErrorMessage =        xeXML.value('(event/data[(@name)[1] eq "message"]/value/text())[1]','varchar(255)')
            , CollectSystemTime =   xeXML.value('(event/action[(@name)[1] eq "collect_system_time"]/text/text())[1]','varchar(255)')
            , ClientAppName =       xeXML.value('(event/action[(@name)[1] eq "client_app_name"]/value/text())[1]','varchar(255)')
            , ClientHostName =      xeXML.value('(event/action[(@name)[1] eq "client_hostname"]/value/text())[1]','varchar(255)')
            , PlanHandle =          CONVERT(xml, xeXML.value('(event/action[(@name)[1] eq "plan_handle"]/value/text())[1]','varchar(255)')).value('(plan/@handle)[1]', 'varchar(255)')
            , SqlText =             xeXML.value('(event/action[(@name)[1] eq "sql_text"]/value/text())[1]','nvarchar(max)')
            , UserName =            xeXML.value('(event/action[(@name)[1] eq "username"]/value/text())[1]','varchar(128)')
        FROM #xmlResults xm
    ) t
    LEFT JOIN sys.databases d ON t.DatabaseID = d.database_id
WHERE t.UserName NOT IN (
      'user_1'  COLLATE SQL_Latin1_General_CP1_CI_AS
    , 'user_2'  COLLATE SQL_Latin1_General_CP1_CI_AS
    , 'user_3'  COLLATE SQL_Latin1_General_CP1_CI_AS
    ) 
ORDER BY t.UserName
    , t.EventDateStamp;

SELECT *
FROM #xmlResults

Context

StackExchange Database Administrators Q#165636, answer score: 8

Revisions (0)

No revisions yet.