none
How Can I audit logins who are accessing my database? RRS feed

Answers

  • Hi Ashif,

    SQL Server error log has entries for every unsuccessful attempt. in case if you want successful attempts as well then there is a configuration change that you will need to make under connections on the SQL Server property page where you can check the box against both successful and unsuccessful logins. however this will only report server level access. if you want more granular one then SQL Server 2008 there is an auditing feature which you can use for a specific object in your case a database. Google it or rather BING it and you will get enough resources on the same or BOL is good starting point.

     

    HTH

    • Marked as answer by Asif_DBA Thursday, August 28, 2014 9:13 AM
    Thursday, August 28, 2014 8:37 AM

All replies

  • Hi Ashif,

    SQL Server error log has entries for every unsuccessful attempt. in case if you want successful attempts as well then there is a configuration change that you will need to make under connections on the SQL Server property page where you can check the box against both successful and unsuccessful logins. however this will only report server level access. if you want more granular one then SQL Server 2008 there is an auditing feature which you can use for a specific object in your case a database. Google it or rather BING it and you will get enough resources on the same or BOL is good starting point.

     

    HTH

    • Marked as answer by Asif_DBA Thursday, August 28, 2014 9:13 AM
    Thursday, August 28, 2014 8:37 AM
  • Using SQL Management Studio you can find it the Property on "Server Properties" -> "Security" -> "Login Auditing" -> "Both failed and successful logins".

    Thursday, August 28, 2014 8:43 AM
  • Not at the database level. But you can at the instance (server) level, as already been mentioned in this thread.

    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, August 28, 2014 2:39 PM
  • If you only want to capture all users accessing a certain database, you can do that by tracing all Events of Locks of type = Shared on Database Scope. This effectively traces the point in time a user accesses the database.

    You can do that with the outdated Profiler or better with Extended Events.


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Friday, September 5, 2014 6:37 PM