Has this login been used recently?

8

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.

8 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/

  3. […] $myData – This is the first result set from the script. Interestingly enough, if any subsequent result sets match the structure of the first, they are appended to the data set. In my opinion this is particularly powerful with a script file. Imagine creating a script that collects data on six different types of clients. They are all clients, and your result sets for each client are the same so all of that information ends up in $myData. In my particular case I used a script to pull data from the SQL log and pull back out the most recent logins/failed logins. […]

  4. […] on has me looping through a number of SQL Instances, grabbing some information (in this case who’d logged in recently), and then writing that information out to a central repository. In this post I’m going to […]

  5. Roi Bailey says:

    Reblogged this on NCBI and commented:
    $bulkCopy = new-object (“Data.SqlClient.SqlBulkCopy”) $OutputConnectionString

  6. […] Using that information to find out when a login was last used. […]

Leave a Reply to A new presentation on Auditing your databases | SQL Studies Cancel 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,614 other followers

Follow me on Twitter

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