Has this login been used recently?
10April 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.
SQL Studies 
[…] Kenneth Fisher checks a box I really like checking: […]
Thanks for sharing!
I recently wrote about this topic as well, so you may find this useful:
Impressive! Thanks for sharing that.
[…] Has this login been used recently? […]
[…] $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. […]
[…] 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 […]
Reblogged this on NCBI and commented:
$bulkCopy = new-object (“Data.SqlClient.SqlBulkCopy”) $OutputConnectionString
[…] Using that information to find out when a login was last used. […]
[…] access to the databases and see who is listed as an owner, and then we start tracking down anyone who’s accessed the databases over the last 30-90 days. Hopefully wherever you are this isn’t quite as difficult, but no […]
[…] cache? You can mine that for any non-maintenance queries applied to a database. Want to know when a user last logged in? The error log can give you […]