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 delelted 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 |
