Has this login been used recently?

3

April 8, 2020 by Kenneth Fisher

I get asked this every now and again, along with the companion When was the last time this login was used? It’s a pretty easy question to answer but there are some caveats. First of all you need to have your system set to log both successful and failed logins. You can probably get away with successful only but personally I want to know a failed attempt just like I’d want to know a successful one.


 
Once that’s done you you’ll have information going forward. Assuming it was already set that way all logins are stored in the SQL Server log files. Which you can search using a script. Here’s mine.

Assuming you use my script, it saves the log information into a temp table which you can then query.

SELECT UserList.UserName, 
	MAX(CASE WHEN #LogInfo.ErrorText LIKE '%succeeded%' THEN LogDate ELSE NULL END) AS LatestSuccess,
	MAX(CASE WHEN #LogInfo.ErrorText LIKE '%failed%' THEN LogDate ELSE NULL END) AS LatestFailure
FROM #LogInfo 
CROSS APPLY (SELECT REPLACE(REPLACE(ErrorText,'Login succeeded for user ''',''),'Login failed for user ''','')) RemoveFront(ErrorText)
CROSS APPLY (SELECT SUBSTRING(RemoveFront.ErrorText,1,CHARINDEX('''', RemoveFront.ErrorText)-1)) AS UserList(UserName)
WHERE #LogInfo.ProcessInfo = 'Logon'
and #LogInfo.ErrorText like 'Login%'
GROUP BY UserList.UserName;

There is probably a better/simpler way to code this, but this is what I came up with. Feel free to suggest something better. I’m certain I’m forgetting something.

Now, on to the caveats.

  • The information only goes back as far back as your error logs do.
  • AD groups are not logged. Only the individual login in is logged.
  • You are only going to get logged in or not. If you want more information you’ll have to do something like a SQL Audit.

 
Speaking of Audits, the main reason I suggest this method instead of an Audit, logon trigger, or anything else, is because it’s quick, easy, and has little to no overhead.

3 thoughts on “Has this login been used recently?

  1. […] Kenneth Fisher checks a box I really like checking: […]

  2. Eitan Blumin says:

    Thanks for sharing!

    I recently wrote about this topic as well, so you may find this useful:

    https://eitanblumin.com/2020/03/09/finding-details-missing-sql-server-failed-logins-audit/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,142 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: