patternsqlMinor
Query list of logins that have logged in the last X days on SQL Server
Viewed 0 times
lastthesqlqueryloginsloggeddaysthatserverlist
Problem
Is there any good query that included internal system objects like sys.dm_exec_sessions or sys.dm_exec_connections that would list logins that have connected to the SQL server over the last X days? 15, 30 days? I am also looking for any other query that may help or related to this.
Solution
As mentioned by @Tibor, unless you set up logging or trace you cannot retrieve that information natively. You do not need to use trigger.
Using SQL Server Management Studio
Configure Login Auditing (SQL Server Management Studio)
Once you set up above you can query the error log and retrieve the information. You can also save the date in a table. I did describe in this blog post how to read from the error log.
Using Extended Event Trace
Audit Login Event Class
The Audit Login event class indicates that a user has successfully logged in to Microsoft SQL Server. Events in this class are fired by new connections or by connections that are reused from a connection pool.
Steve Jones explained in detail in his blog post: Tracking Logins with Extended Events
Using SQL Server Management Studio
Configure Login Auditing (SQL Server Management Studio)
Once you set up above you can query the error log and retrieve the information. You can also save the date in a table. I did describe in this blog post how to read from the error log.
Using Extended Event Trace
Audit Login Event Class
The Audit Login event class indicates that a user has successfully logged in to Microsoft SQL Server. Events in this class are fired by new connections or by connections that are reused from a connection pool.
Steve Jones explained in detail in his blog post: Tracking Logins with Extended Events
Context
StackExchange Database Administrators Q#259623, answer score: 4
Revisions (0)
No revisions yet.