Simple Question..login audit

• Mathew J Kulangara

  SSCommitted

  Points: 1610

  More actions

  August 1, 2005 at 10:42 am

  #168096

  Does anyone know what tables if any track user logins when the audit option is turned on?

   

  TIA

  ---

  Mathew J Kulangara
  sqladventures.blogspot.com

• CJohnson-232084

  Default port

  Points: 1492

  More actions

  August 2, 2005 at 6:48 am

  #578803

  The logins are recorded in the error log.  As far as I know, the login event is not stored in a table.

  I use the following query to filter log data.  You could modify the WHERE clause to look for entries of interest.

   

  DROP TABLE #Errors

  CREATE TABLE #Errors (vchMessage varchar(255), ID int)

  CREATE INDEX idx_msg ON #Errors(ID, vchMessage)

  INSERT #Errors EXEC master..xp_readerrorlog

  SELECT vchMessage FROM #Errors

  WHERE vchMessage NOT LIKE '%Log backed up%'

    AND vchMessage NOT LIKE '%Database backed up%'

    AND vchMessage NOT LIKE '%.TRN%'

    AND vchMessage NOT LIKE '%.BAK%'

    AND vchMessage NOT LIKE '%Copyright (c)%'

    AND vchMessage NOT LIKE '%All rights reserved%'

    AND vchMessage NOT LIKE '%SurfControl_web%'

    AND vchMessage NOT LIKE '%Error: 9002%'

    AND vchMessage NOT LIKE '%DBCC CHECKDB%'

  ORDER BY  ID

   

   

  Hope this helps.