I'm very new to SQL, so really need some tips.

I need some help building a query that'll get me all users that have logged into to our platform over the past 12 months.

  • 1
    Where are you logging this data?
    – Tara Kizer
    Nov 1 '16 at 18:13
  • This is dangerously close to 'too broad', it may get closed. You'll need to give us more information. Are you aware of any process that actually logs this data? If so, can you provide the table definition? Nov 1 '16 at 18:15

In short, you can't do this unless you configure some logon auditing in advance. Your options to do that are:

  • Configure Login Auditing. This will log all successful logins to the SQL Server event log. I usually advise against this due to the volume of logins written
  • SQL Server Audit: create an audit to write all logins to a file or the event log.
  • Extended Events: create an extended events session to write all logins to a file. There is a pre-defined template to achieve this, create a session via the wizard and select the "connection tracking" template
  • Logon Triggers: create a logon trigger and write logins to a table adding any further bespoke logic you choose. For example you can just update a logon count for each login so you don't insert massive volumes of data.

Not the answer you're looking for? Browse other questions tagged or ask your own question.