2

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.

0

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;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.