Sql-server – Error while creating Extended Events on 2008 SQL Server

extended-eventssql serversql-server-2008

I am trying to create an extended event to capture the login information of my server. While creating an extended event on SQL 2008 using the below query 

CREATE EVENT SESSION [SA_Monitor] ON SERVER
ADD EVENT sqlserver.login(
ACTION(sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)

I am getting the following error.

The event name, "sqlserver.login", is invalid, or the object could not
be found.

On Initial investigation I found that creating extended events using SSMS was introduced from 2012 and the only method to create EE is through SQL query. Is there a generic query that can help me capture this information in the 2008 server.

Best Answer

You can use a logon trigger to record the event info.

USE yourDatabase;     -- select name from sys.databases;

--IF (OBJECT_ID('LogonInfo') IS NOT NULL)   -- select name from sys.tables where name = 'LogonInfo'
--  DROP TABLE LogonInfo;

CREATE TABLE LogonInfo
(
    LogonTime   DATETIME,
    LoginName   VARCHAR(50),
    ClientHost  VARCHAR(50),
    LoginType   VARCHAR(50)
);


--  SELECT * FROM yourDatabase..LogonInfo ORDER BY LogonTime DESC
--  SELECT DISTINCT LoginName FROM yourDatabase..LogonInfo ORDER BY LoginName


USE [master];

--IF EXISTS (SELECT * FROM sys.server_triggers WHERE [type] = 'TR' AND [name] = 'LogonInfoTrigger')
--  DROP TRIGGER LogonInfoTrigger ON ALL SERVER;

CREATE TRIGGER LogonInfoTrigger
ON ALL SERVER WITH EXECUTE AS '<serviceAccount>'   -- select name from sys.server_principals;
FOR LOGON
AS
BEGIN
    -- Check if the log table exists before trying to do the insert
    -- Otherwise the trigger will error and not allow anyone to log in
    IF (OBJECT_ID('yourDatabase..LogonInfo') IS NOT NULL)
    BEGIN
        DECLARE 
            @LogonTriggerData xml,
            @EventTime datetime,
            @LoginName varchar(50),
            @HostName varchar(50),
            @LoginType varchar(50);

        SET @LogonTriggerData = eventdata();

        SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime');
        SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)');
        SET @HostName = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)');
        SET @LoginType = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)');

        -- use the if statment if you want to ignore certain accounts
        -- remove the if statement and just run the insert statement if you want to capture all accounts
        IF @LoginName NOT IN ('yourSafeAccount1','yourSafeAccount2')
        BEGIN
            INSERT INTO yourDatabase..LogonInfo (LogonTime, LoginName, ClientHost, LoginType)
            VALUES (@EventTime, @LoginName, @HostName, @LoginType);
        END;
    END;
END;
Related Question