Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

Here is a run down 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 hestiate 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 Entreprise and Datacenter editions I can't seem to find a specific article from Books Online that says otherwise.

USE MASTER
GO

CREATE SERVER AUDIT audit_test                                           
TO FILE (FILEPATH = 'D:\Audit')
GO


CREATE SERVER AUDIT SPECIFICATION audit_test_spec       
FOR SERVER AUDIT [audit_test]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE=ON)
GO

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

CREATE SERVER AUDIT (Transact-SQL)

CREATE SERVER AUDIT SPECIFICATION (Transact-SQL)

CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)

share|improve this answer
    
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? –  carlo.borreo Dec 6 '13 at 8:10
    
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. –  Craig Efrein Dec 6 '13 at 8:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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