Using SQL Trace to Audit Database Access

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/trace.asp

  • Hi,

    There is one mistake in the beginning of the scripts. Instead of DROP PROCEDURE usp_Login_Audit_Trace, the article says DROP PROCEDURE dbo.spRoleMembers. My apologies and much thanks to reader William Nabers. Got to be careful with "cut and paste"....

    The correct script is at the end of this post.

    In addition, there might be some compilation errors when you copy and paste the script to Query Analyzer. I think that's due to changes to formatting during the upload of the article to the web server. You might need to make some minor adjustment in order for it to work.

    Thanks.

    Haidong

     --Use the master database 
    

    USE master

    go

    IF OBJECT_ID('dbo.usp_Login_Audit_Trace') IS NOT NULL

    DROP PROCEDURE dbo.usp_Login_Audit_Trace

    GO

    CREATE proc usp_Login_Audit_Trace

    @path nvarchar(128),

    @duration smallint

    as

    /*

    Author: Haidong Ji

    Date: 11/08/2002

    Purpose: Login audit. Try to track which logins and which databases are accessed

    Set up trace to a file in the path specified in the @path parameter (use UNC path for network drive) for the number of days/hours/minutes specified in the @duration input parameter.

    */

    declare @tracestarttime datetime

    declare @traceident int

    declare @options int

    declare @filename nvarchar(245)

    declare @filesize bigint

    declare @tracestoptime datetime

    declare @createcode int

    declare @on bit

    declare @startcode int



    set @tracestarttime = current_timestamp

    /* Set the name of the trace file. */

    set @filename = cast(month(current_timestamp) as varchar) + '_' +

    cast(day(current_timestamp) as varchar) + '_' +

    cast(year(current_timestamp) as varchar) + '_' +

    cast(datepart(hh, current_timestamp) as varchar) + '_' +

    cast(datepart(mi, current_timestamp) as varchar) + '_' +

    cast(datepart(ss, current_timestamp) as varchar)

    set @options = 2

    set @filename = @path + N'\' + @filename

    set @filesize = 20

    /* You can change the first parameter in the dateadd function to set how long your trace will be

    For example, if it is hh, the trace will last @duration hours */

    set @tracestoptime = dateadd(dd, @duration, @tracestarttime)

    set @on = 1

    --set up the trace

    exec @createcode = sp_trace_create @traceid = @traceident output, @options = @options,

    @tracefile = @filename, @maxfilesize = @filesize, @stoptime = @tracestoptime

    if @createcode = 0

    --trace created

    begin

    --set events and columns

    --Trace Login event

    exec sp_trace_setevent @traceident, 14, 1, @on

    exec sp_trace_setevent @traceident, 14, 6, @on

    exec sp_trace_setevent @traceident, 14, 7, @on

    exec sp_trace_setevent @traceident, 14, 8, @on

    exec sp_trace_setevent @traceident, 14, 9, @on

    exec sp_trace_setevent @traceident, 14, 10, @on

    exec sp_trace_setevent @traceident, 14, 11, @on

    exec sp_trace_setevent @traceident, 14, 12, @on

    exec sp_trace_setevent @traceident, 14, 14, @on

    exec sp_trace_setevent @traceident, 14, 18, @on

    exec sp_trace_setevent @traceident, 14, 34, @on

    exec sp_trace_setevent @traceident, 14, 35, @on





    --Trace Logout event

    exec sp_trace_setevent @traceident, 15, 1, @on

    exec sp_trace_setevent @traceident, 15, 6, @on

    exec sp_trace_setevent @traceident, 15, 7, @on

    exec sp_trace_setevent @traceident, 15, 8, @on

    exec sp_trace_setevent @traceident, 15, 9, @on

    exec sp_trace_setevent @traceident, 15, 10, @on

    exec sp_trace_setevent @traceident, 15, 11, @on

    exec sp_trace_setevent @traceident, 15, 12, @on

    exec sp_trace_setevent @traceident, 15, 14, @on

    exec sp_trace_setevent @traceident, 15, 18, @on

    exec sp_trace_setevent @traceident, 15, 34, @on

    exec sp_trace_setevent @traceident, 15, 35, @on

    --Trace Audit Object GDR event

    exec sp_trace_setevent @traceident, 103, 1, @on

    exec sp_trace_setevent @traceident, 103, 6, @on

    exec sp_trace_setevent @traceident, 103, 7, @on

    exec sp_trace_setevent @traceident, 103, 8, @on

    exec sp_trace_setevent @traceident, 103, 9, @on

    exec sp_trace_setevent @traceident, 103, 10, @on

    exec sp_trace_setevent @traceident, 103, 11, @on

    exec sp_trace_setevent @traceident, 103, 12, @on

    exec sp_trace_setevent @traceident, 103, 14, @on

    exec sp_trace_setevent @traceident, 103, 18, @on

    exec sp_trace_setevent @traceident, 103, 34, @on

    exec sp_trace_setevent @traceident, 103, 35, @on

    --Trace Audit Add/Drop Login event

    exec sp_trace_setevent @traceident, 104, 1, @on

    exec sp_trace_setevent @traceident, 104, 6, @on

    exec sp_trace_setevent @traceident, 104, 7, @on

    exec sp_trace_setevent @traceident, 104, 8, @on

    exec sp_trace_setevent @traceident, 104, 9, @on

    exec sp_trace_setevent @traceident, 104, 10, @on

    exec sp_trace_setevent @traceident, 104, 11, @on

    exec sp_trace_setevent @traceident, 104, 12, @on

    exec sp_trace_setevent @traceident, 104, 14, @on

    exec sp_trace_setevent @traceident, 104, 18, @on

    exec sp_trace_setevent @traceident, 104, 34, @on

    exec sp_trace_setevent @traceident, 104, 35, @on

    --Trace Audit Login GDR event

    exec sp_trace_setevent @traceident, 105, 1, @on

    exec sp_trace_setevent @traceident, 105, 6, @on

    exec sp_trace_setevent @traceident, 105, 7, @on

    exec sp_trace_setevent @traceident, 105, 8, @on

    exec sp_trace_setevent @traceident, 105, 9, @on

    exec sp_trace_setevent @traceident, 105, 10, @on

    exec sp_trace_setevent @traceident, 105, 11, @on

    exec sp_trace_setevent @traceident, 105, 12, @on

    exec sp_trace_setevent @traceident, 105, 14, @on

    exec sp_trace_setevent @traceident, 105, 18, @on

    exec sp_trace_setevent @traceident, 105, 34, @on

    exec sp_trace_setevent @traceident, 105, 35, @on

    --Trace Audit Login Change Property event

    exec sp_trace_setevent @traceident, 106, 1, @on

    exec sp_trace_setevent @traceident, 106, 6, @on

    exec sp_trace_setevent @traceident, 106, 7, @on

    exec sp_trace_setevent @traceident, 106, 8, @on

    exec sp_trace_setevent @traceident, 106, 9, @on

    exec sp_trace_setevent @traceident, 106, 10, @on

    exec sp_trace_setevent @traceident, 106, 11, @on

    exec sp_trace_setevent @traceident, 106, 12, @on

    exec sp_trace_setevent @traceident, 106, 14, @on

    exec sp_trace_setevent @traceident, 106, 18, @on

    exec sp_trace_setevent @traceident, 106, 34, @on

    exec sp_trace_setevent @traceident, 106, 35, @on


    --Trace Audit Add Login to Server Role event

    exec sp_trace_setevent @traceident, 108, 1, @on

    exec sp_trace_setevent @traceident, 108, 6, @on

    exec sp_trace_setevent @traceident, 108, 7, @on

    exec sp_trace_setevent @traceident, 108, 8, @on

    exec sp_trace_setevent @traceident, 108, 9, @on

    exec sp_trace_setevent @traceident, 108, 10, @on

    exec sp_trace_setevent @traceident, 108, 11, @on

    exec sp_trace_setevent @traceident, 108, 12, @on

    exec sp_trace_setevent @traceident, 108, 14, @on

    exec sp_trace_setevent @traceident, 108, 18, @on

    exec sp_trace_setevent @traceident, 108, 34, @on

    exec sp_trace_setevent @traceident, 108, 35, @on

    --Trace Audit Add DB User event

    exec sp_trace_setevent @traceident, 109, 1, @on

    exec sp_trace_setevent @traceident, 109, 6, @on

    exec sp_trace_setevent @traceident, 109, 7, @on

    exec sp_trace_setevent @traceident, 109, 8, @on

    exec sp_trace_setevent @traceident, 109, 9, @on

    exec sp_trace_setevent @traceident, 109, 10, @on

    exec sp_trace_setevent @traceident, 109, 11, @on

    exec sp_trace_setevent @traceident, 109, 12, @on

    exec sp_trace_setevent @traceident, 109, 14, @on

    exec sp_trace_setevent @traceident, 109, 18, @on

    exec sp_trace_setevent @traceident, 109, 34, @on

    exec sp_trace_setevent @traceident, 109, 35, @on

    --Trace Audit Add Member to DB event

    exec sp_trace_setevent @traceident, 110, 1, @on

    exec sp_trace_setevent @traceident, 110, 6, @on

    exec sp_trace_setevent @traceident, 110, 7, @on

    exec sp_trace_setevent @traceident, 110, 8, @on

    exec sp_trace_setevent @traceident, 110, 9, @on

    exec sp_trace_setevent @traceident, 110, 10, @on

    exec sp_trace_setevent @traceident, 110, 11, @on

    exec sp_trace_setevent @traceident, 110, 12, @on

    exec sp_trace_setevent @traceident, 110, 14, @on

    exec sp_trace_setevent @traceident, 110, 18, @on

    exec sp_trace_setevent @traceident, 110, 34, @on

    exec sp_trace_setevent @traceident, 110, 35, @on

    --Trace Audit Add/Drop Role event

    exec sp_trace_setevent @traceident, 111, 1, @on

    exec sp_trace_setevent @traceident, 111, 6, @on

    exec sp_trace_setevent @traceident, 111, 7, @on

    exec sp_trace_setevent @traceident, 111, 8, @on

    exec sp_trace_setevent @traceident, 111, 9, @on

    exec sp_trace_setevent @traceident, 111, 10, @on

    exec sp_trace_setevent @traceident, 111, 11, @on

    exec sp_trace_setevent @traceident, 111, 12, @on

    exec sp_trace_setevent @traceident, 111, 14, @on

    exec sp_trace_setevent @traceident, 111, 18, @on

    exec sp_trace_setevent @traceident, 111, 34, @on

    exec sp_trace_setevent @traceident, 111, 35, @on

    --filter Profiler

    exec sp_trace_setfilter @traceid = @traceident, @columid = 10, @logical_operator = 0, @comparison_operator = 7, @value = N'SQL Profiler'

    --start the trace

    exec @startcode = sp_trace_setstatus @traceid = @traceident, @status = 1

    if @startcode = 0

    begin

    select 'Trace started at ' + cast(@tracestarttime as varchar) + ' for ' + cast(@duration as varchar)+

    ' minutes; trace id is ' + cast(@traceident as nvarchar) + '.'
    end
    else
    begin
    goto Error
    end
    end
    else
    begin
    goto Error
    end

    return

    Error:
    select 'Error starting trace.'
    return
    GO
  • I have tried to execute the listed stored procedure. the procedure does execute and the file is created but with no data in it (0 KB)

  • Deema,

    Thanks for reading my article.

    It's normal for the file size to be 0 when the trace just got started. SQL Trace does not write to this file right away. For a short trace (a couple of minutes), the size will be 0kb until the trace is over. For a long trace, the trace size will increase periodically.

    To view all the running trace, use the following T-SQL:

    SELECT * FROM :: fn_trace_getinfo(default)

    To stop a trace, use the following T-SQL:

    sp_trace_setstatus @traceId, 0

    Hope this helps

    Haidong

    http://www.sqlservercentral.com/columnists/hji

  • hi, we've implemented this on our sql server and it's working a treat, however, I've become greedy and also want to get the objectid of the object that is being traced (in particular DTS Designer

    and SQLAgent - Job Manager, i've tried adding the following line into the stored procedure for each Trace Event block...

    exec sp_trace_setevent @traceident, XX, 22, @on

    however nothing is returned. I'm not an sql guru so it may be something really fundamental that I'm not understanding, can you help?

  • shoayb,

    I am glad you found this article helpful. In fact, you could easily edit that stored procedure and use it to trace deadlocks, do performence tuning and lots of other things.

    >>exec sp_trace_setevent @traceident, XX, >>22, @on

    >>however nothing is returned.

    I am not too sure what you mean by "Nothing is returned"

    1. If the ObjectID column is added to the trace output file but the data column is empty, then that is normal, because some columns are not applicable to certain events;

    2. If the ObjectID column does not exist in the output file, that means this column was not added to the trace. There are 2 ways to resolve this:

    a. You could add this column on the fly:

    1. Use SELECT * FROM :: fn_trace_getinfo(default) to find out your trace ID;

    2. Use sp_trace_setstatus @traceId, 0. This will pause the trace but not kill it;

    3. You can then issue exec sp_trace_setevent @traceid, XX, 22, @on to add this column;

    4. After you are done, issue SP_trace_setstatus @traceId, 1 to restart the trace;

    b. This is the permanent solution

    1. Use SELECT * FROM :: fn_trace_getinfo(default) to find out your trace ID;

    2. Use sp_trace_setstatus @traceId, 0. This will pause the trace but not kill it;

    3. Use sp_trace_setstatus @traceId, 2. This will kill the trace; NOTE: YOU MUST USE STEP 2 AND 3 TO KILL AND DELETE A TRACE. SIMPLY USING sp_trace_setstatus @traceId, 2 WILL NOT WORK;

    4. Edit the your stored procedure and add the data column there and you are good to go.

    Hope this helps.

    Haidong

    http://www.sqlservercentral.com/columnists/hji

  • Hi Haidong,

    You answered my question with...

    "that is normal, because some columns are not applicable to certain events".

    After I posted my question I found 2 very useul articles...

    http://www.sqlmag.com/Articles/Index.cfm?ArticleID=23016

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_6koi.asp

    The second one details which columns are applicable for given events.

    Thanks for your help and reply.

  • Haitong, how to make your script to do the trace automatically whenever the server is reboot ?

    ddfg

  • awu,

    Assuming SQLSERVERAGENT service starts automatically, you can create a job that runs this procedure. When schedule this job, under Schedule Type, pick the "Start automatically when SQL Server Agent starts" option.

    Hope this helps.

    Haidong

  • Haidong, thank you for your reply.

    I used the extended stored procedure created a couple of trace running on SQL 7, we are in the process of upgrading to SQL 2000.

    In SQL 7, there is an extended stored procedure: xp_trace_setqueueautostart

    which will make the trace automatically started whenever the server is reboot. But i didn't find the same thing in SQL 2000, that's why i asking the question.

    I thought the way you mentioned before, it will restart the trace whenever the Agent is restart, but what happen if you only restart the Agent without restarting the SQL Server ?

    Again, thank you for your help.

    ddfg

  • awu,

    Tracing and profling have been totally redesigned for SQL 2k, that's why you couldn't see xp_trace_setqueueautostart and other extended stored procedures for tracing.

    When you start SQL Agent, SQL Server Service will be started automatically, as SQL Agent service is dependent on SQL Server service.

    Hope this helps.

    Haidong

  • No, Haidong, I don't agree with you on "When you start SQL Agent, SQL Server Service will be started automatically, as SQL Agent service is dependent on SQL Server service."

    For my understanding, it’s true that when you restart SQL Server, the SQL Agent will restarted automatically, but you are able to restart Agent without restart SQL Server, so my question is, If we put a job to run the your script automatically when the Agent is start, then do we need to check if the existing trace still active, and remove it first before we create another one ? I am afraid we may create multi trace in the system.

    Again, thank you for your time.

    ddfg

  • awu,

    I suggest you DO the following:

    1. Stop both SQL Server Service and SQL Server Agent service;

    2. Start SQL Server Agent service. Then verify to see if SQL Server service starts automatically;

    3. Again, stop both SQL Server Service and SQL Server Agent service. Now start SQL Server Service. Check SQL Agent service and see if it is started.

    After you've done the above, assuming you are running Win2K or above and go to Computer Management. Then go to Services. Make sure SQL Agent service's startup type is Automatic. For WinNT, go to Control Panel, then Services.

    Yes, you are able to stop and restart SQL Agent without affecting SQL Server service. I don't have time to test that scenario, but I would agree that the existing trace would still be active, if it is not timed out or stopped manually.

  • Haidong,

    You are right in your sense, I think we are just not in the same page for the same question, my original concern is :

    if you put the sp in a job and configure it as "Start automatically when SQL Server Agent starts", then what happen if you only restart the Agent without restarting the SQL Server ? I am afraid we may create multiple trace in the system.

    And you actually give me the answer by saying:

    Yes, you are able to stop and restart SQL Agent without affecting SQL Server service. I don't have time to test that scenario, but I would agree that the existing trace would still be active, if it is not timed out or stopped manually.

    And i test it today, it did keeping create new active trace in the system each time you stop and start Agent service, i fixed this simply put the following the first line inside the SP:

    IF EXISTS (select * from ::fn_trace_getinfo(0) where property=5 and value=1) Return

    But be aware of the fact, this assume you have only one active trace on the server, and it's true in my system.

    Just wanna share this with you, again, thank you for your help, it's great script.

    ddfg

Viewing 15 posts - 1 through 15 (of 22 total)

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