Click on the banner to learn about and purchase my database training on Azure

SQL Server - How to know the date of a user's last login

Views: 5.941
This post is the 16 part of 16 in the series. Security and Audit
Reading Time: 7 minutes

Speak guys!
In this post, I would like to bring you a solution on how to know the date of a user's last login to SQL Server, which is information that I often see someone wanting to know how to obtain, especially to identify the users who access the environment or not , to make data mappings and / or exclude users who are not accessing the bank anymore.

I will demonstrate some solutions that can meet this need, but none of them is something native to SQL Server created specifically for this purpose, such as a simple last_login column in a view like sys.server_principals, for example.

Solution # 1 - Accdate column in sys.syslogins (DOES NOT resolve)

Very common “solution” to find on blogs and forums to solve this type of problem, this query NO returns the date of a user's last login, as many people believe. The sys.syslogins catalog view is marked as “deprecated” since SQL Server 2005 and since SQL 2008, at least, we can identify in the source code of that view, that the “accdate” column always has the same value as the “ createdate ”

Solution # 2 - MAX (login_time) at sys.dm_exec_sessions (DOES NOT resolve)

Another totally wrong “solution” that I see on many websites, blogs and forums, the use of the DMV sys.dm_exec_sessions could NEVER be used to find out the date of a user's last login, as this DMV only shows information from active sessions of the instance.

If the instance is restarted or the session ends running, for example, that information will already be lost. If when your last login data collection routine is performed and you have no more active sessions for that user, you will also not have this information available.

For these reasons, I do not consider this consultation as a solution to this problem.

Solution # 3 - Using Login Auditing

Starting now with solutions that actually work, this solution that I will present consists of enabling the audit of logins successfully also (the default is to audit only failures) and use the errorlog to identify the login dates of users.

The negative point of this solution is the number of new records that will appear in the errorlog. In addition to consuming more space, it can make troubleshooting more difficult using the errorlog, due to the very high number of lines, depending on the number of instance logins.

How to enable this option using SSMS:

How to enable this option using T-SQL:

Script used to monitor collecting data:

Result:

With this script above, you just need to create a job to run it periodically and collect the necessary data. The dbo.LastLogin table will have the most current data on the last login of each user.

Solution # 4 - Using a logon trigger

Another way for you to create this logins history is to use logon triggers for this. This is a solution where you have more control than using the Login Auditing feature, but at the same time, it takes a little more work to create.

The negative point of this solution is that, like every logon trigger, if you create something wrong in the development of the trigger or the user who is going to log in does not have permission in the history table, this will PREVENT him from being able to log into the bank. So be very careful with this, and test a lot before applying in production.

To learn more about logon triggers, read my article SQL Server - How to implement audit and control of logins (Logon Trigger).

Creation of history tables

Creation of Trigger

Generates the analysis of each user's last login

Result:

Solution # 5 - Using Server Audit

Another possible solution is to use the Server Audit feature of SQL Server, which I already demonstrated here on the blog how to use it through the article Audit on SQL Server (Server Audit).

In this solution, which is very light from the point of view of resource consumption, I will create a server audit and a server audit specification, and monitor the occurrences of the SUCCESSFUL_LOGIN_GROUP event. I will insert the data returned in the history table and then analyze the results obtained.

Creation of history tables

Creation of Server Audit

Creation of the Server Audit Specification

Generates the analysis of each user's last login

Result:

Well folks, that's it! I hope you enjoyed these solutions that I proposed in this article and that they can help in your day to day.

Are there other ways to monitor this? Leave it here in the comments
A big hug and even more!