none
SQL Audit in SQL2005 SP4

    General discussion

  • Hi,

    I have been asked to give report of successful and unsuccessful database users login based on database since last 30 days for Audit purpose. But unfortunately we have not had enabled the audit option on the server property and trace as well before. But still we need to provide the report now, is it possible? If so how can we achieve it?

    Thanks in Advance

    Dave

    Friday, November 15, 2013 8:21 AM

All replies

  • If you have not set up Auditing before, you cannot get the data from the past.

    Most servers have set up Logging of FAILED Logins at Server level, and depeding on your SQL Server Error-Log history, you can get those from there.


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Friday, November 15, 2013 10:11 AM
  • If you are not explicitly disabled login audit, failed logins are audited in SQL server.

    Regards,

    Praveen

    Friday, November 15, 2013 10:22 AM
  • Thanks Andreas,

    But Failed logins only at the server level and not database level right? Is 3rd party tool can get this all info?

    Friday, November 15, 2013 10:40 AM
  • Hi Dave

    Logon always happens at Server Level - unless you are using contained databases (http://technet.microsoft.com/en-us/library/ff929071.aspx).

    Therefore you can only capture it at Server Level.

    For a contained database you would have to use the Auditing feature. - http://technet.microsoft.com/en-us/library/dd392015%28v=sql.100%29.aspx

    For all others there is no way to get such an event. You could imitate it by looking for shared locks at database level (using extended events), but still, there is no way to get it back from the past.


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com


    Friday, November 15, 2013 10:51 AM
  • logins exist only server level, in database level you have users.

    Regards,

    Praveen

    Friday, November 15, 2013 10:53 AM
  • Yes Praveen, my question also about database users only like who, when users logged in and out
    Friday, November 15, 2013 10:56 AM
  • Logon always happens at Server Level - unless you are using contained databases
    And contained databases are not available on SQL 2005.

    If someone at when logging in specified a database to which he did not have access, this will be recoerded as a login failure.

    But if someone who is already logged in, attempts to access a database to which he does not have access, I don't believe this is recorded at all. At least I can't see any trace event. But maybe this is available in SQL Server Audit, but again that feature is not available in SQL 2005.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, November 15, 2013 10:08 PM
  • totally true - in the midst of writing and while making sure not to forget about exceptions, I forgot about the title, which implied 2005
    It might be useful for other users though, so I leave it unchanged.

    To continue from what Erland added:
    There is in fact no special event for accessing a database. Failures are also not logged because they are not critical.

    As extended events are not available, your only alternative to the server settings is SQL Trace (Profiler)
    Yet without a special event, you still have to revert to the alternative I mentioned: Trace Shared Locks on Database level.

    In all cases: no history.


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Saturday, November 16, 2013 11:49 AM
  • You can try the below tool and then analyse the logs if the transaction logs are available on the server.

    http://www.red-gate.com/products/dba/sql-log-rescue/	
    Sunday, November 17, 2013 11:35 PM