2

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.

  • 1
    Do you have any type of logging turned on? SQL Server do not keep track of successful logins by default. – Tibor Karaszi Feb 13 at 15:02
  • is there any logging I can turn on which is native to SQL without turning on any triggers, etc? – PolDBQ Feb 13 at 15:06
  • See my answer... – Tibor Karaszi Feb 13 at 18:18
  • For both of the answers below, if you start recording all this extra on a really busy system it can cause significant space issues. – James Jenkins Feb 13 at 19:11
4

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

  • i did turn on login auditing for successful logins, restarted sql server, but when i query using the statement you provided in your blog post, i get no results. Any ideas? – PolDBQ Feb 19 at 14:04
  • Do you see the successful login events in your error log? Did you change the value of '@searchString1' and '@searchString2' in my script? – SqlWorldWide Feb 19 at 15:39
  • changing the @searchString1 value did the trick and i can now see the results – PolDBQ Feb 19 at 19:54
  • If I answered the result to your satisfaction, do you mind mark the answer as accepted so other users can take benefit? – SqlWorldWide Feb 19 at 21:20
2

OK, so no logging turned on at this point. First step is to turn on the type of logging that suits you. Ones that come to mind are:

  • Turning on the logging of also successful logins in the registry, using SSMS. As described by @SqlWorldWide in another answer. This goes to the EventLog, and also the errorlog file.
  • Using XE trace. Again, already explained by @SqlWorldWide.
  • Using Server Audit functionality (which is build on XE). Using XE directly is probably more flexible for your purposes, but perhaps Server Audit can give you insights/info/functionality that using XE directly don't have.
  • Using the old trace engine, "SQL Trace". This is deprecated, so I wouldn't go this route.
  • Using Logon Triggers. Your trigger would not most likely insert a row into a table, which you later query.

It is difficult for us to recommend one over the other (except that I don't recommend the old trace engine). Only you know what you are more comfortable with and your requirements. Using any of the XE based methods or Logon Triggers feels a bit more robust for me when it comes to querying the data.

For the XE based methods, you can query a trace file involving some XQuery. Below is an example of a SQL query containing XQuery to parse the XML into columns. Not directly based on an audit trace, but it gives you an idea.

--XML from file
SELECT CAST(event_Data AS xml) AS event_data 
FROM sys.fn_xe_file_target_read_file('R:\SqlStatements*.xel', NULL, NULL, NULL)

--Each event is one row

DROP TABLE IF EXISTS #myXeData

SELECT CAST(event_Data AS XML) AS StatementData
INTO #myXeData
FROM sys.fn_xe_file_target_read_file('R:\SqlStatements*.xel', NULL, NULL, NULL)

SELECT * FROM #myXeData


--Use XQuery to transform to table. 
SELECT 
 StatementData.value('(event/@name)[1]', 'varchar(50)') AS event_name
,StatementData.value('(event/@timestamp)[1]', 'datetime2(0)') AS time_stamp
,StatementData.value('(event/action[@name="database_name"]/value)[1]','nvarchar(128)') AS database_name_
,StatementData.value('(event/data[@name="duration"]/value)[1]','bigint') / 1000 AS duration_ms
,StatementData.value('(event/data[@name="cpu_time"]/value)[1]','bigint') /1000 AS cpu_ms
,StatementData.value('(event/data[@name="physical_reads"]/value)[1]','bigint') AS physical_reads
,StatementData.value('(event/data[@name="logical_reads"]/value)[1]','bigint') AS logical_reads
,StatementData.value('(event/data[@name="row_count"]/value)[1]','bigint') AS row_count
,StatementData.value('(event/data[@name="statement"]/value)[1]','nvarchar(50)') AS statement_
FROM #myXeData AS evts
--ORDER BY time_stamp
ORDER BY duration_ms DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.