Best way to Audit logins

• rummings

  SSCarpal Tunnel

  Points: 4575

  More actions

  May 19, 2015 at 12:31 pm

  #398477

  I have inherited a SQL Cluster with 150+ databases. Is there an easy... painless way to monitor which accounts are actually needed?

  Thanks!

  Charlie

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  May 19, 2015 at 12:43 pm

  #1799132

  You can enable a server side trigger that logs every login.

  You can log both successful and unsuccessful login attempts (security tab of server properties)

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• Colleen M. Morrow

  SSCrazy

  Points: 2457

  More actions

  May 21, 2015 at 11:30 am

  #1799786

  Like Jason said, a login trigger can be implemented to track login events to a log table. You can also use event notifications to do something similar, if you don't like the idea of a trigger. A server-side trace can be used, too. All three of those options are available in Standard and Enterprise edition. Depending on what version/edition you're on, you can also use SQL Audit to audit login events.

  ---

  
  Colleen M. Morrow
  Cleveland DBA