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.