I have a Microsoft SQL Server 2008r2 SP1.

I need to know which users connect to, and disconnect from, database.

Ideally, I would like a list of (datetime, username, connect/disconnect).

Can this be done using extended events? Or is there a better way?

1 Answer 1


Here is a rundown of some of the options I have used to audit logins and logouts. I haven't had the chance to use Extended Events for auditing purposes yet. Below, I quickly describe tracking login and logout events using SQL Server tracing and SQL Server Audits. You'll probably end up going with setting up background server tracing which is described briefly below. If you need me to go into further detail, don't hesitate to ask.

Default Trace

The default trace is one way to audit activity on your server without making any changes. A post on what you can get out of the default trace is provided here.

Server Trace

If you are just looking for Login/Logout information, you can also create your own server trace to audit logins and logouts using SQL Server profiler. Just create a new trace and then export it as a SQL Server Trace definition file.

  1. Open up SQL Server Profiler
  2. Create a new trace using the blank template
  3. Go to the Events selection tab
  4. Under Security Audit, check Audit Login and Audit Logout

Run your trace, stop it and then under File, you can Export your trace as a SQL Server trace definition file. You can then run that trace in the background and save it to a file which can later be imported into a SQL Server table.

A portion of an example server Trace created by profiler:

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5 

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 2, @on
exec sp_trace_setevent @TraceID, 14, 66, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

Here is the output I got from just capturing those items:

enter image description here

It is possible to convert server traces into extended event sessions and that is described in detail here.

Server Audits

There are also server audits which allows you to track and monitor Database/Server level events. Auditing may only be available in the Enterprise and Datacenter editions. I can't seem to find a specific article from Books Online that says otherwise.


CREATE SERVER AUDIT audit_test                                           

FOR SERVER AUDIT [audit_test]

You can then view server audit events in SSMS by going to Security -> Audits. Then using the context menu, you can select View Audit Logs to review activity.

enter image description here

enter image description here

For more information on audits, you will find additional information here:




  • I have Enterprise edition. I executed the commands you give for server audit, but after several hours "View Audit Logs" shows no activity, but I know we had at least hundreds of connection. What can I be doing wrong? Dec 6, 2013 at 8:10
  • 1
    Hello @carlo.borreo. Did you make sure the audit was enabled? Under Security -> Audits -> name of your audit, right click and enable if that is not the case. Dec 6, 2013 at 8:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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