Improving clients MSSQL platform and they have 400+ service accounts, many with sysadmin privileges and likely only a sub set are being used. Versions are 2008/R2 and 2012.
What are the best options to capture all DB access attempts and filter the logins. Server Side Trace, XE (2012) or is there another less hassle method. End goals is to quantity x amount of logins did access databases, x amount of logins did not.
Might be the answer to the wrong question! but you could put a Trigger on Login and store the details to a table (in a database of your choosing)
Seen "other" DBAs use a trigger technique for logins many years ago with disastrous results. No one could access prod. Granted - your method could be completely different and thanks for suggesting.
Is there no DMV or System Catalogue which stores every login used to connect since last engine service cycle
No it isn't ... sorry about that, but there is a documented "recovery" route from that problem. I would definitely want to do a Dress Rehearsal of the back-out before putting it live. Good thing you mentioned that for anyone else reading this thread in future - if they are a bit Gung-Ho!
I don't know the answer, but you can record that in SQL Profiler, and all the logins (both success and/or failure) can, optionally, be logged in the Error Log file ... so my guess is that there will be a DMW for that. But (unless you build something to log that, rather than using the Ring Buffer) I presume there is the risk that periodic inspection of DMV may have some gaps in the data (which may or may not be important)
Thanks Kirsten. That' why I posted as believe this is a typical DBA problem and would be ideal to gather methods and techniques from the MSSQL community and really appreciate your ideas.
The lowest technical effort might rule out XE option as there are pre ss2012 machines (maybe even ss2005). Server Side Trace works on all versions, or could build customised job using DMVs to track and insert results into table. I use sp_WhoIsActive within a 5 second loop, 24/7 and could query the login_name column against the machines login's list and generate list that way. Will look at the results I get from here and quantify whether useful to use as pseudo login audit.
We run a trace continuously to capture this information. We just use a plain old scripted trace. The key is to filter for non-pooled logins. The column for that is "event subclass" (I think) but I can't remember the actual values.