SQL Profiler Traces - Login Events only

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Not Tested

Credits:

Author: ChillyDBA

Date: 7 Jun 2020

Description

This version of the SP does what it says on the label - traces only login events.

Very useful for finding unused logins.

Code

/****** Object: StoredProcedure [dbo].[usp_ProfilerServerSideTrace_Execute] Script Date: 12/10/2018 11:40:53 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[usp_ProfilerServerSideTrace_Logins_Execute]

@Folder NVARCHAR(500), -- name of the output folder

@Job_Name VARCHAR(100), -- name of the job that is scheduled

@Trace_Purpose VARCHAR(100), -- just to help us remember why we did it.

--also forms part of the file name (and table name if loaded into DB)

@Trace_Duration BIGINT, --#minutes that the trace will run for

@Trace_MaxFileSize BIGINT, --#MB that the trace file will be allowed to grow to.

--Trace will stiop at this point regadless of the duration setting

-- ** NOTE ** -- data types for the filter values must match the data type of the trace column being filtered.

-- These can be looked up in SQL BOL

@Filter_DB_Value NVARCHAR(100), -- the database to be monitored

@Filter_User_Value NVARCHAR(100) -- the user name to be monitored

AS

/***************************************************************************************

Purpose: Checks constructs server side trace metadata and runs a trace it.

Intended for unattended profiler trace runs.

Lower load than running through GUI

Scope of this SP:

1. Operates at Server level

2. Creates trace instance

3. Adds trace counters (a fixed set)

4. Adds trace filters (determined by parameters)

5. Starts the trace

Note: Trace instance automatically deleted on completion

--select * from msdb.sys.traces

-- in case it is not deleted, use the command below and insert the correct @TraceID from the results of the statmeent above

-- EXEC sp_trace_setstatus @TraceID, 2

Author: Andy Hughes

History: 10 Apr 2018

10 Dec 2018 - v2 traces login events only

only login and DB filters available

***************************************************************************************/

SET NOCOUNT ON

-- Declare local variables

DECLARE

@StopTime DATETIME,

@StartDateString VARCHAR(13),

@On BIT,

@ComparisonOperatorID INT,

@Servername SYSNAME,

@TraceID INT,

@TraceFile NVARCHAR(1000),

@TraceName NVARCHAR(1000),

@intfilter INT

--initialise local variables

SELECT

@StopTime = DATEADD(mi,@Trace_Duration,GETDATE()), -- add the trace duration offset to current datetime

@StartDateString = CONVERT(CHAR(8),GETDATE(),112) -- generate a datetime string for use in the file name --['YYYYMMDD_HHMM']

+ '_'

+ CAST(REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),':','') AS CHAR(4)),

@On = 1, -- used to strart the trace

@ServerName = REPLACE(@@SERVERNAME, '\','_') + '_' -- create a server name string for the file name - replace '\' with '_' for instance names

-- add a '\' to the end of the folder string if it doesn't already have one.

IF RIGHT(@Folder,1)<>'\'

SELECT @Folder = @Folder + '\'

-- now create the trace name and trace filename strings from their component parts

SELECT @TraceName = 'Trace_' + @ServerName + @StartDateString + '_' + @Trace_Purpose

SELECT @TraceFile = @Folder + @TraceName

-- create the trace instance and record the ID in @TraceID

EXEC sp_trace_create

@TraceID = @TraceID OUTPUT,

@options = 0,

@tracefile = @TraceFile,

@maxfilesize = @Trace_MaxFileSize,

@stoptime = @StopTime

-- if the trace didn't create correctly, then quit.

IF (ISNULL(@TraceID, 1) < 1) RETURN(-1)

BEGIN

-- Set the trace events and columns

/*

These are a subset of events and columns and are ones that I have found to be the most

commonly used.

Events

14 - Login event

Column IDs

1 TextData;

3 DatabaseID

6 NTUserName;

9 ClientProcessID;

10 ApplicationName;

11 LoginName;

12 SPID;

13 Duration;

14 StartTime;

15 EndTime;

16 Reads;

17 Writes;

18 CPU;

21 EventSubClass;

27 EventClass;

35 DatabaseName;

*/

-- add trace columns for Login

EXEC sp_trace_setevent @TraceID, 14, 1, @On

EXEC sp_trace_setevent @TraceID, 14, 3, @On

EXEC sp_trace_setevent @TraceID, 14, 6, @On

EXEC sp_trace_setevent @TraceID, 14, 9, @On

EXEC sp_trace_setevent @TraceID, 14, 10, @On

EXEC sp_trace_setevent @TraceID, 14, 11, @On

EXEC sp_trace_setevent @TraceID, 14, 12, @On

EXEC sp_trace_setevent @TraceID, 14, 13, @On

EXEC sp_trace_setevent @TraceID, 14, 14, @On

EXEC sp_trace_setevent @TraceID, 14, 15, @On

EXEC sp_trace_setevent @TraceID, 14, 16, @On

EXEC sp_trace_setevent @TraceID, 14, 17, @On

EXEC sp_trace_setevent @TraceID, 14, 18, @On

EXEC sp_trace_setevent @TraceID, 14, 21, @On

EXEC sp_trace_setevent @TraceID, 14, 27, @On

EXEC sp_trace_setevent @TraceID, 14, 35, @On

-- Set the trace Filters

-- NOTE: variables are used for the BIGINT filter values as this SP does not seem to accept

-- values with or without or CAST statements

-- use sp_trace_setfilter <TraceID>, <ColumnID>, <Logical Operator>, <Comparison Operator>, <Comparison Value>

-- <Logical Operator>: 0 = AND, 1 == OR. AND will always be used here

-- standard exclustion for Application Name to exlude profiler

EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler '

END

-- now set the filters defined by the values stored agains the trace definition and passed as parameters

-- but only if they are not null and valid

--set up the DB name value filter if supplied

IF (@Filter_DB_Value IS NOT NULL)

BEGIN

SELECT

@ComparisonOperatorID = 6, -- this will always be a LIKE operation

@Filter_DB_Value = N'%' + @Filter_DB_Value + N'%'

EXEC sp_trace_setfilter @TraceID, 35, 0, @ComparisonOperatorID, @Filter_DB_Value

END

--set up the Login Name value filter if supplied

IF (@Filter_User_Value IS NOT NULL)

BEGIN

SELECT

@ComparisonOperatorID = 6, -- this will always be a LIKE operation

@Filter_User_Value = N'%' + @Filter_User_Value + N'%'

EXEC sp_trace_setfilter @TraceID, 11, 0, @ComparisonOperatorID, @Filter_User_Value

END

-- Set the trace status to start

EXEC sp_trace_setstatus @TraceID, 1

RETURN;

SET NOCOUNT OFF

GO