locked
Audit Login Logout using SQL Trace via Transact SQL RRS feed

  • Question

  • Hi,

    I have written below SQL statement to create and run a SQL trace. However i donot see any trace file created in specified location. What am i doing wrong?

    When i execute the below, i get RC=0 and TraceID = 2 in RESULTS window.


    DECLARE @RC int, @TraceID int, @on BIT
    EXEC @rc = sp_trace_create @TraceID output, 0, N'\\sdohhqfil01\Server Archives\Database Archives\SDOHHQDBA09\ProductionAuditLogin'

    --Select the return code to see if the trace creation was successful.
    SELECT RC = @RC, TraceID = @TraceID

    -- Set the events and data columns you need to capture.
    SELECT @on = 1

    -- 10 is RPC:Completed event. 1 is TextData column.
    --14 is Audit Login, 11 Login Name
    EXEC sp_trace_setevent @traceID, 14, 11, @on
    --13 application name
    EXEC sp_trace_setevent @traceID, 14, 10, @on
    --14 start time
    EXEC sp_trace_setevent @traceID, 14, 14, @on
    --35 Database Name
    EXEC sp_trace_setevent @traceID, 14, 35, @on
    --3 Database ID
    EXEC sp_trace_setevent @traceID, 14, 3, @on
    --26 Server Name
    EXEC sp_trace_setevent @traceID, 14, 26, @on
    --23 Success
    EXEC sp_trace_setevent @traceID, 14, 23, @on
    --12 SPID
    EXEC sp_trace_setevent @traceID, 14, 12, @on

    --15 is Audit Logout, 11 Login Name
    EXEC sp_trace_setevent @traceID, 15, 11, @on
    --10 application name
    EXEC sp_trace_setevent @traceID, 15, 10, @on
    --14 start time
    EXEC sp_trace_setevent @traceID, 15, 14, @on
    --14 End Time
    EXEC sp_trace_setevent @traceID, 15, 15, @on
    --13 Duration
    EXEC sp_trace_setevent @traceID, 15, 13, @on
    --35 Database Name
    EXEC sp_trace_setevent @traceID, 15, 35, @on
    --3 Database ID
    EXEC sp_trace_setevent @traceID, 15, 3, @on
    --26 Server Name
    EXEC sp_trace_setevent @traceID, 15, 26, @on
    --16 Reads
    EXEC sp_trace_setevent @traceID, 15, 16, @on
    --17 Writes
    EXEC sp_trace_setevent @traceID, 15, 17, @on
    --23 Success
    EXEC sp_trace_setevent @traceID, 15, 23, @on
    --12 SPID
    EXEC sp_trace_setevent @traceID, 15, 12, @on

    --20 is Audit Login failed, 11 Login Name
    EXEC sp_trace_setevent @traceID, 20, 11, @on
    --10 application name
    EXEC sp_trace_setevent @traceID, 20, 10, @on
    --14 start time
    EXEC sp_trace_setevent @traceID, 20, 14, @on
    --35 Database Name
    EXEC sp_trace_setevent @traceID, 20, 35, @on
    --3 Database ID
    EXEC sp_trace_setevent @traceID, 20, 3, @on
    --26 Server Name
    EXEC sp_trace_setevent @traceID, 20, 26, @on
    --23 Success
    EXEC sp_trace_setevent @traceID, 20, 23, @on
    --12 SPID
    EXEC sp_trace_setevent @traceID, 20, 12, @on

    -- Set any filter: Database ID = 13
    EXEC sp_trace_setfilter @traceID, 3, 0, 0, 13

    -- Start Trace (status 1 = start)
    EXEC @RC = sp_trace_setstatus @traceID, 1
    GO

    Sunday, April 11, 2010 7:43 AM

Answers

  • It did create a trace file for me on a local disk.  Can you try local disk?

    Here is how you can SELECT from a trace table:

    SELECT * 
    FROM fn_trace_gettable('f:\temp\login.trc', default);
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by RubSay Monday, April 12, 2010 8:38 AM
    Sunday, April 11, 2010 12:37 PM
  • Hi,

     

    I executed the mentioned code, trace file is getting create.

    Check for the write permission on the mentioned folder.

    Alternatively you can check your code by executing the code on your local machine.

    Is it giving any error?

    Thanks,

     

     

    • Marked as answer by RubSay Monday, April 12, 2010 8:38 AM
    Sunday, April 11, 2010 12:37 PM
  • http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/fc404e01-496b-4f25-a3a2-13b23e2ef7c0

    As Already told by Kalman, I would suggest you need to create a trace on the local disk (on sql server computer),

    looks like you are creating on a network share, if that is the case make sure account under which SQL server is running can access that share (i.e. has write permission on that share)

    • Marked as answer by RubSay Monday, April 12, 2010 8:38 AM
    Sunday, April 11, 2010 2:56 PM

All replies

  • It did create a trace file for me on a local disk.  Can you try local disk?

    Here is how you can SELECT from a trace table:

    SELECT * 
    FROM fn_trace_gettable('f:\temp\login.trc', default);
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by RubSay Monday, April 12, 2010 8:38 AM
    Sunday, April 11, 2010 12:37 PM
  • Hi,

     

    I executed the mentioned code, trace file is getting create.

    Check for the write permission on the mentioned folder.

    Alternatively you can check your code by executing the code on your local machine.

    Is it giving any error?

    Thanks,

     

     

    • Marked as answer by RubSay Monday, April 12, 2010 8:38 AM
    Sunday, April 11, 2010 12:37 PM
  • http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/fc404e01-496b-4f25-a3a2-13b23e2ef7c0

    As Already told by Kalman, I would suggest you need to create a trace on the local disk (on sql server computer),

    looks like you are creating on a network share, if that is the case make sure account under which SQL server is running can access that share (i.e. has write permission on that share)

    • Marked as answer by RubSay Monday, April 12, 2010 8:38 AM
    Sunday, April 11, 2010 2:56 PM
  • Thanks Kalman and Mayur.

    I was executing the above in Mgt Studio from my local workstation. And the trace file was being created on C drive on the SQL Server. Gosh i goofed up. Your comments helped me realise my error.

    I am ok with creating it locally on the server rather than a share.

    Thanks.

    Monday, April 12, 2010 8:36 AM