September 26, 2016 at 1:51 pm
My server ERRORLOG files record every "Logon" to our databases. The logs show large numbers of messages saying "Login succeeded for user 'domain\username' "
How can I report who has logged on, and how many times?
We are using SQL Server 2012 SP3.
Thank you for your help.
September 26, 2016 at 1:58 pm
With login success being logged to the error log, you don't, not without a nasty amount of text processing. It's one of the more useless settings I've seen, makes the error log unusable and is almost useless as auditing)
Turn login success logging off and set up SQL Audit, or an Extended Event session to track what you need to track, those you can report from effectively.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 27, 2016 at 8:41 am
Thank you - you've confirmed that all my web searching was for naught. Time to think about writing some code to parse the ERRORLOG files.
September 28, 2016 at 7:04 am
I strongly recommend you move the login auditing to SQLAudit or Extended Events rather.
They're far more suitable for this kind of audit, and doing so means that your error log is for *errors* and is usable as an error log. As it is, you could be having severity 24 errors every hour and you'd never see it in the error log because of all the login success messages.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply