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