Post reply

Logging user access

  • October 21, 2010 at 6:00 am

    #143193

    Hi everybody,

    What is the best way to log user access to olap server?

    I need to log only which users logged in, not the queries which have been executed or the data accessed.

    Thanks,

    Michael

  • October 21, 2010 at 6:14 am

    #1240225

    You can set up a logon trigger and record logins to a table of your choice.

    Here's a sample:

    CREATE TRIGGER [TR_LOGON]

    ON ALL SERVER

    FOR LOGON

    AS

    BEGIN

    UPDATE Logons SET last_logon = GETDATE()

    WHERE login_name = ORIGINAL_LOGIN()

    INSERT INTO Logons (login_name, last_logon)

    SELECT ORIGINAL_LOGIN(), GETDATE()

    WHERE ORIGINAL_LOGIN() NOT IN (

    SELECT login_name

    FROM Logons

    )

    END

    Edit: fixed code tags

    -- Gianluca Sartori

  • October 21, 2010 at 7:06 am

    #1240273

    Hey Gianluca,

    Thanks for your answer!

    Is there a way to log the client application name as well?

    Thanks!

  • October 21, 2010 at 8:20 am

    #1240351

    Yes. You just have to query sys.dm_exec_sessions:

    SELECT program_name

    FROM sys.dm_exec_sessions

    WHERE session_id = @@spid

    -- Gianluca Sartori

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply