Using the Service Audit Object in SQL Server 2008

  • K. Brian Kelley

    SSC Guru

    Points: 114445

    Comments posted to this topic are about the item Using the Service Audit Object in SQL Server 2008

    K. Brian Kelley
    @kbriankelley

  • anand.ramanan

    Mr or Mrs. 500

    Points: 507

    You mention that SQL Server Audit object is available in any version of SQL other than Express, however, Audit is only available in 2008 Enterprise. Is there a water down version of auditing available in versions other than 2008. What alternatives are available to perform similar auditing when not using Enterprise edition?

  • K. Brian Kelley

    SSC Guru

    Points: 114445

    What I said in the article was that just about every edition above Express *could* use it. But Microsoft chose to only put it in Enterprise Edition. For those of us who started using Standard Edition for 2-node HA clusters, this hurts. Standard Edition is economical and meets our needs. Enterprise Edition is pricey. But if we want to use the Audit object, we've got no choice but to use Enterprise.

    As far as alternatives, there's nothing similar to it in the other versions.

    K. Brian Kelley
    @kbriankelley

  • Anipaul

    SSC-Insane

    Points: 24681

    Great article...

  • andynold

    SSC Journeyman

    Points: 83

    What are recommended options for auditing tools and/or utilities for people that are using the MS-SQL standard and web editions?

    We have a public facing web site where our biggest auditing need (in addition to what we can see in the regular OS event logs) is to track DBA actions.

  • Atiq Rahman-266579

    SSC Veteran

    Points: 256

    Hi Brian,

    Very nice article. I was able to configure the auditing but I am not getting the parameter values in the audit.

    Ex. If some executed a query as follows:

    select name, age from dbo.employees where empno = 100.

    This above query is being audited as follows:

    select name, age from dbo.employees where empno = @1.

    How can I get the value of @1?

    Regards,

    Atiq Rahman

  • Atiq Rahman-266579

    SSC Veteran

    Points: 256

    Hi Brian,

    Very nice article. I was able to configure the auditing but I am not getting the parameter values in the audit.

    Ex. If some executed a query as follows:

    select name, age from dbo.employees where empno = 100.

    This above query is being audited as follows:

    select name, age from dbo.employees where empno = @1.

    How can I get the value of @1?

    Regards,

    Atiq Rahman

  • K. Brian Kelley

    SSC Guru

    Points: 114445

    This may be one of the weaknesses in the Audit Object. I need to play around a bit more to see if you can capture the parameter, but at first glance, I'm not sure you can.

    K. Brian Kelley
    @kbriankelley

  • abbala2008

    SSC Enthusiast

    Points: 100

    Hi,

    Nice article....You have done good job....Thank you.....

    ---------------------------------------

    Auditing Tools

  • Wayne West

    SSC-Insane

    Points: 22586

    Brian, thanks much for this article! It was exactly what I needed to get a test system going to explore what the audit offered. I had been fiddling with it to no avail, read your article, and had it running shortly thereafter.

    I was a little disappointed that the file export was to log format, I was hoping I could get it out as a CSV. There is the export function, I think I'll be looking in to automating dumping the contents to a text file.

    I think it's basically a good thing, but it still has some growth.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • Ian_McCann

    SSCertifiable

    Points: 5128

    Great article.

    Is there a way to roll over the logs on a time basis or manually?

    I'm thinking of some sort of

    DBCC ERRORLOG

    sp_cycle_errorlog

    equivilant

  • Achilies

    SSCommitted

    Points: 1549

    Good article,

    I know to view audit logs, we can right click from SSMS to the audit specification and view them if the object has made the destination to a file. But how to view the audit logs, if the destination has given to windows/application log. Right now i am viewing the audit logs (given to windows/application log) going to event viewer and opening the windows logs. Is there any way that we view these right from SSMS?

    Thanks..

  • Ian_McCann

    SSCertifiable

    Points: 5128

    Beginner_2008 (3/5/2012)


    Good article,

    I know to view audit logs, we can right click from SSMS to the audit specification and view them if the object has made the destination to a file. But how to view the audit logs, if the destination has given to windows/application log. Right now i am viewing the audit logs (given to windows/application log) going to event viewer and opening the windows logs. Is there any way that we view these right from SSMS?

    Thanks..

    I do not have my audit files set up this way, so can not be sure, but have you looked under Management, SQL ServerLogs. here you can select logs for Windows NT, application

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

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