Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56

    Unanswered: Security Audit on MSSQL

    I'm historically an Oracle admin, with some of MSSQL.
    No bias, each has its strengths and weeknesses,
    just a matter of circumstance.

    However, I've inherited an environment with a majority of MSSQL. (2000/2005)
    I've gotten along just fine until just this moment.

    I'm wondering if I'm asking too much, or just can't find the right documentation.
    I'm sure you've gotten this question before, but I can't seem to find the thread.

    ----

    I would like to create a security audit similar to what I do on Oracle.
    Start the audit utility, set commands to trigger, log to a table
    and have it run behind the scenes.

    Now while Profiler will allow a perfect set of triggers and logging to a table.
    It won't continue automatically, and turns off when I exit.

    I've seen some cmd line sql statements that will create a file automatic.
    But I'd rather avoid a file and direct to a table. (for ad-hoc querying)

    Same with the sp_ commands I've found.

    Is there any way of setting up a security audit this way?
    - output to table
    - run behind the scenes
    - continuously until purged
    - all this together

    Or am I just not seeing the obvious...

    Thank you very much.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What do you want included in your security audit?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    First, logging directly to a table is a very bad idea in busy environments. Second, you can script the trace definition, open it in QA or SSMS, specify the file with rollover after no more than 500MB, and run the script. It will create and start the trace for you in the background. Third, for ad-hoc querying you do not need to send the output to a table (and you won't be able to if you follow the instructions for #2). All you need to know is the location of the first trace file:
    select * from ::fn_trace_gettable('C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\log_4.trc', 0)

    To find out where the trace files are look up ::fn_trace_getinfo in BOL.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...and this script will display the contents of all 5 default trace files that are currently maintained by SQL2K5 instance:
    Code:
       declare @ErrorDumpDir nvarchar(512), @TraceFile nvarchar(2048)
       exec master.dbo.xp_instance_regread
          N'HKEY_LOCAL_MACHINE'
         ,N'SOFTWARE\Microsoft\MSSQLServer\CPE'
         ,N'ErrorDumpDir'
         ,@ErrorDumpDir output
       set @TraceFile = (
          select top 1 fFullName
             from __admin.fn__DIR(@ErrorDumpDir, '*.trc', 0)
             order by fLastAccessTime
          )
       select * from ::fn_trace_gettable(@TraceFile, 0)
          order by StartTime
    unfortunately you will need a CLR assembly which I am not posting here yet that helps you get the very first trace file in the chain of 5. However, you see the concept here.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •