Welcome to EMC Consulting Blogs

SELECT  cat.name            AS category_name
        ,evt.name           AS event_name
        ,col.name           AS column_name
        ,sub.subclass_name 
FROM    sys.trace_subclass_values sub
JOIN    sys.trace_columns col           ON  sub.trace_column_id = col.trace_column_id
JOIN    sys.trace_events evt            ON  sub.trace_event_id = evt.trace_event_id
JOIN    sys.trace_categories cat        ON  cat.category_id = evt.category_id
WHERE   sub.subclass_name like '%pool%'

 

Clearly then we need to open up the Security Audit category and select the audit Login event.  To get at this information we must ensure to pick the EventSubClass column.  If we were using Profiler to build this trace it would look something like this

However, being a good tracer I’d actually want to set my trace up server side and drop the results to a file. To do this I’d export the definition I have just created using File | Export | Script Trace Definition | For SQL Server 2005 – 2008…

 

I’d then have a script that looked something like this:

 

/****************************************************/
/* Created by: SQL Server 2008 Profiler             */
/* Date: 25/07/2009  20:52:50         */
/****************************************************/


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

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

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, 7, @on
exec sp_trace_setevent @TraceID, 14, 23, @on
exec sp_trace_setevent @TraceID, 14, 8, @on
exec sp_trace_setevent @TraceID, 14, 64, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 21, @on  --EventSubClass
exec sp_trace_setevent @TraceID, 14, 41, @on
exec sp_trace_setevent @TraceID, 14, 49, @on
exec sp_trace_setevent @TraceID, 14, 57, @on
exec sp_trace_setevent @TraceID, 14, 2, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 26, @on
exec sp_trace_setevent @TraceID, 14, 66, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 35, @on
exec sp_trace_setevent @TraceID, 14, 51, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 60, @on
exec sp_trace_setevent @TraceID, 20, 7, @on
exec sp_trace_setevent @TraceID, 20, 23, @on
exec sp_trace_setevent @TraceID, 20, 31, @on
exec sp_trace_setevent @TraceID, 20, 8, @on
exec sp_trace_setevent @TraceID, 20, 64, @on
exec sp_trace_setevent @TraceID, 20, 9, @on
exec sp_trace_setevent @TraceID, 20, 21, @on --EventSubClass
exec sp_trace_setevent @TraceID, 20, 49, @on
exec sp_trace_setevent @TraceID, 20, 57, @on
exec sp_trace_setevent @TraceID, 20, 6, @on
exec sp_trace_setevent @TraceID, 20, 10, @on
exec sp_trace_setevent @TraceID, 20, 14, @on
exec sp_trace_setevent @TraceID, 20, 26, @on
exec sp_trace_setevent @TraceID, 20, 30, @on
exec sp_trace_setevent @TraceID, 20, 3, @on
exec sp_trace_setevent @TraceID, 20, 11, @on
exec sp_trace_setevent @TraceID, 20, 35, @on
exec sp_trace_setevent @TraceID, 20, 51, @on
exec sp_trace_setevent @TraceID, 20, 12, @on
exec sp_trace_setevent @TraceID, 20, 60, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec
 sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 180a71e3-2916-4eb5-b5cf-cb625d702f39'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

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

error: 
select ErrorCode=@rc

finish: 
go

 

Note you still have to make some changes to this definition Notably you might want to make the file roll over and you’ll definitely want to change the filename. However, to all intents and purposes you are now good to go and your trace file will generate you something like this:

 

Hopefully that’s given you enough of a taster to look into this yourself.  If you want to know more you might be unsurprised to know that I am writing the SQL Trace chapter for this new SQL Internals book I am working on.  I got quite excited the other day as it appeared on www.amazon.co.uk for the first time.  My name isn’t up in lights just yet (I think they need to do another refresh from the publishers as I joined the project late) however it is very exciting.

 

Till the next time,

 

James