locked
Auditing in SQL Server 2008 (or 2008 R2) RRS feed

  • Question

  • Hi, I believe that audit specifications cannot be used in the standard version of SQL 2008 and 2008 R2 ? If this is the case, how can I create an audit trail on a table or view which records info such as Active Directory account name (of query executor), date and time of query, query details (ie. select statement), etc etc for each select query executed on the table or view.

    Any help much appreciated, my client cannot upgrade to enterprise (and therefore use audit specifications) to to cost limitations.

    Thursday, October 21, 2010 3:59 PM

Answers

All replies

  • Hi, I believe that audit specifications cannot be used in the standard version of SQL 2008 and 2008 R2 ? If this is the case, how can I create an audit trail on a table or view which records info such as Active Directory account name (of query executor), date and time of query, query details (ie. select statement), etc etc for each select query executed on the table or view.

    Any help much appreciated, my client cannot upgrade to enterprise (and therefore use audit specifications) to to cost limitations. 

    • Merged by Tom Li - MSFT Monday, October 25, 2010 8:17 AM duplicate thread
    Thursday, October 21, 2010 3:26 PM
  • Hi, 

    With SQL server 2008 and 2008 R2 standard edition you couldn't perform auditing. But you can do the default trace for the basic auditing or you can write custom script for the auditing purpose. I advice you to use trigger is the best and a powerfull way to detect the date and time of a specific query and details, also you can use the logon trigger ect...

    Thursday, October 21, 2010 4:29 PM
  • I think what you are looking for, is here http://msdn.microsoft.com/en-us/library/dd392015(SQL.100).aspx. Hope this helps.
    Thanks, Mohan Kumar - www.sqlvillage.com -- Please mark the post as answered if it answers your question.
    • Marked as answer by rtdh Friday, October 22, 2010 11:44 PM
    Thursday, October 21, 2010 9:19 PM
  • As you are aware, SQL Server Audit can only be done on Enterprise and Datacenter editions of SQL Server 2008 R2.

    http://msdn.microsoft.com/en-us/library/cc645993.aspx

    However, the article mentioned by Mohan briefly mentions SQL Server Trace (SQL Profiler is the GUI).  SQL Server Trace can be used to trace all batches that run on your server.  This will give you a good degree of auditing, although not at the level that the SQL Server Audit provides.  There have been articles on this:

    http://www.sqlmag.com/article/auditing/get-compliant-with-sql-server-2005-audit-logging.aspx
    http://www.sqlservercentral.com/articles/Administration/auditingwithsqlprofiler/1461/

    And others as well.  People used SQL Server Trace and Profiler for years before SQL Server Audit and doubtless many who cannot afford the more expensive versions of SQL Server will continue to do so. 

    RLF

    • Marked as answer by rtdh Friday, October 22, 2010 11:44 PM
    Friday, October 22, 2010 12:56 AM
  • You may refer to thread for same topic which can be helpful in meeting your requirement. Here is link http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/8930cdae-777b-4f25-beac-1a754912cbbe

     


    Thanks, Mohan Kumar - www.sqlvillage.com -- Please mark the post as answered if it answers your question.
    Friday, October 22, 2010 4:59 AM