locked
Login auditing RRS feed

  • Question

  • Hi all,

    Can anyone suggest how to audit the login activity? Using SQL SSMS we already configured On the Security page, under Login auditing we enabled this.

    But is there any other way or any scripts to get insert/update and delete information for the logins? 

    All we want to know is we have one dedicated 'sa' login for that we need to monitor complete operations. So please suggest me any queries /methods to get this information.

    Thanks all,

    DBA

    Friday, March 31, 2017 10:28 AM

All replies

  • Hi,

    1. Login Auditing captures basic information like user login/logout sessions. 
    2. For detailed information like Insert/Update/Delete statements executed by particular user you need to enable SQL Server Audit provided SQL Server Audit available 2008 onward and restricted to Edition in 2008/R2(Enterprise only).

    Friday, March 31, 2017 10:55 AM
  • If you want to track exactly _who_ is doing _what_, you will have limited success if you users are logging in with 'sa' account, since it is a 'shared' credential (ie: multiple people could use it).

    In order to track activity to a specific user, consider restricting logins to Windows domain accounts, instead of 'sa' (or any other shared SQL Login that can potentially be used by multiple users).

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    Friday, March 31, 2017 12:25 PM
  • Database auditing is available in lower editions as of 2016 sp1. But, as Phil mentioned, if all are logged in as "sa", then all we will see is that "sa" did all the modifications.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, April 4, 2017 7:02 PM