Enable non sysadmins to run a profiler trace for a set period of time and view the results of the trace

My customer is a large corporate with a large Microsoft Development team. This team develops and suports numerous in house applications. These developers quite often need to troubleshoot problems on these apps and them having the ability to run a profiler trace greatly improves their ability to effectively troubleshoot problems. Obviously sysadmin rights on production servers for Developers is not allowed and not a good idea. Even the developers agree. With SQL 2005 and 2008 assigning sufficient permissions to achieve the above is not too difficult.

I've encapsulated everything the developers need into one stored procedure that takes 1 input paramater, run duration for the trace, with a default of 5 minutes.

The developers AD group is given login rights to a production SQL Server that they need to run the trace on. Then the ALTER TRACE Server permission is given to this group. The enables them to create a profiler trace (using sp_trace_create) and start (sp_trace_setstatus) the trace. The trace that's created by the stored procedure is set to capture RPC:Completed, SQL:BatchCompleted events as well as the CPU usage, Duration, Reads and Writes and some other information. The trace writes to a .trc file to the root of the C drive. The run duration input parameter for the sp specifies how long the trace runs for. Once the trace run time is done the trace is stopped and closed. The trace file is then imported into a table in msdb called PerfTrace. This is done using the fn_trace_gettable system function. Finally the PerfTrace table is queried and the results displayed to the user. The code is reasonably self explanatory.

USE [MSDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PerfTrace](
 [TextData] [ntext] NULL,
 [BinaryData] [image] NULL,
 [DatabaseID] [int] NULL,
 [TransactionID] [bigint] NULL,
 [LineNumber] [int] NULL,
 [NTUserName] [nvarchar](256) NULL,
 [NTDomainName] [nvarchar](256) NULL,
 [HostName] [nvarchar](256) NULL,
 [ClientProcessID] [int] NULL,
 [ApplicationName] [nvarchar](256) NULL,
 [LoginName] [nvarchar](256) NULL,
 [SPID] [int] NULL,
 [Duration] [bigint] NULL,
 [StartTime] [datetime] NULL,
 [EndTime] [datetime] NULL,
 [Reads] [bigint] NULL,
 [Writes] [bigint] NULL,
 [CPU] [int] NULL,
 [Permissions] [bigint] NULL,
 [Severity] [int] NULL,
 [EventSubClass] [int] NULL,
 [ObjectID] [int] NULL,
 [Success] [int] NULL,
 [IndexID] [int] NULL,
 [IntegerData] [int] NULL,
 [ServerName] [nvarchar](256) NULL,
 [EventClass] [int] NULL,
 [ObjectType] [int] NULL,
 [NestLevel] [int] NULL,
 [State] [int] NULL,
 [Error] [int] NULL,
 [Mode] [int] NULL,
 [Handle] [int] NULL,
 [ObjectName] [nvarchar](256) NULL,
 [DatabaseName] [nvarchar](256) NULL,
 [FileName] [nvarchar](256) NULL,
 [OwnerName] [nvarchar](256) NULL,
 [RoleName] [nvarchar](256) NULL,
 [TargetUserName] [nvarchar](256) NULL,
 [DBUserName] [nvarchar](256) NULL,
 [LoginSid] [image] NULL,
 [TargetLoginName] [nvarchar](256) NULL,
 [TargetLoginSid] [image] NULL,
 [ColumnPermissions] [int] NULL,
 [LinkedServerName] [nvarchar](256) NULL,
 [ProviderName] [nvarchar](256) NULL,
 [MethodName] [nvarchar](256) NULL,
 [RowCounts] [bigint] NULL,
 [RequestID] [int] NULL,
 [XactSequence] [bigint] NULL,
 [EventSequence] [bigint] NULL,
 [BigintData1] [bigint] NULL,
 [BigintData2] [bigint] NULL,
 [GUID] [uniqueidentifier] NULL,
 [IntegerData2] [int] NULL,
 [ObjectID2] [bigint] NULL,
 [Type] [int] NULL,
 [OwnerID] [int] NULL,
 [ParentName] [nvarchar](256) NULL,
 [IsSystem] [int] NULL,
 [Offset] [int] NULL,
 [SourceDatabaseID] [int] NULL,
 [SqlHandle] [image] NULL,
 [SessionLoginName] [nvarchar](256) NULL,
 [PlanHandle] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

USE [MSDB]
GO

CREATE PROC usp_startTrace5
@Runtime VARCHAR(10) = '00:05:00'
AS
BEGIN

DECLARE @RC int, @TraceID int, @on bit, @maxfilesize BIGINT, @stoptime DATETIME, @FileName nvarchar(256), @Dt VARCHAR(20), @VAL BIGINT 
SET @Val = 1000
set @on = 1
SET @maxfilesize = 100
SET @Dt = CONVERT(VARCHAR(20), GETDATE(), 105)+'_'+CONVERT(VARCHAR(10), DATEPART(Hh, GETDATE()))+CONVERT(VARCHAR(10), DATEPART(mi, GETDATE()))
--SET @stoptime = DATEADD(mi, 1, GETDATE()) 
SET @FileName = 'C:\PerfTrace5_'+CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(50))+'_'+@Dt

EXEC @rc = sp_trace_create @TraceID output, 0, @FileName, @maxfilesize, NULL 
-- Set the events and data columns you need to capture.
EXEC sp_trace_setevent @TraceID, 10,  1, @On --1 = Text Data, RPC:Completed
EXEC sp_trace_setevent @TraceID, 12,  1, @On --1 = Text Data, SQL:BatchCompleted
EXEC sp_trace_setevent @TraceID, 10,  3, @On --3 = DatabaseID
EXEC sp_trace_setevent @TraceID, 12,  3, @On --3 = DatabaseID
EXEC sp_trace_setevent @TraceID, 10, 12, @On -- 12 = SPID
EXEC sp_trace_setevent @TraceID, 12, 12, @On -- 12 = SPID
EXEC sp_trace_setevent @TraceID, 10, 13, @On -- 13 = Duration
EXEC sp_trace_setevent @TraceID, 12, 13, @On -- 13 = Duration
EXEC sp_trace_setevent @TraceID, 10, 14, @On -- 14 = StartTime
EXEC sp_trace_setevent @TraceID, 12, 14, @On -- 14 = StartTime
EXEC sp_trace_setevent @TraceID, 10, 16, @On -- 16 = Reads
EXEC sp_trace_setevent @TraceID, 12, 16, @On -- 16 = Reads 
EXEC sp_trace_setevent @TraceID, 10, 17, @On -- 17 = Writes
EXEC sp_trace_setevent @TraceID, 12, 17, @On -- 17 = Writes
EXEC sp_trace_setevent @TraceID, 10, 18, @On -- 18 = CPU
EXEC sp_trace_setevent @TraceID, 12, 18, @On -- 18 = CPU
EXEC sp_trace_setevent @TraceID, 10, 26, @On -- 26 = ServerName 
EXEC sp_trace_setevent @TraceID, 12, 26, @On -- 26 = ServerName 

-- Set any filter by using sp_trace_setfilter.
--EXEC sp_trace_setfilter @TraceID, 13, 0, 2, @Val This would filter out any queries that run for shorter than 1 second

-- Start the trace.
EXEC sp_trace_setstatus @TraceID, 1;

WAITFOR DELAY @Runtime

EXEC sp_trace_setstatus @TraceID, 0;
EXEC sp_trace_setstatus @TraceID, 2;

WAITFOR DELAY '00:00:05'

TRUNCATE TABLE msdb.dbo.PerfTrace;
declare @FN VARCHAR(100)
SET @FN = @FileName+'.trc'
--select @FN
INSERT PerfTrace Select * FROM ::fn_trace_gettable(@FN, default);

SELECT TextData, db.name as DatabaseName, Duration, Starttime, Reads, Writes, CPU, SPID      
FROM [MSDB].[dbo].[PerfTrace] p INNER JOIN master.sys.databases db
on p.DatabaseID = db.database_ID
WHERE DatabaseID > 4

END

USE MASTER
GO

GRANT ALTER TRACE TO [Domain\group]

USE MSDB 
GO

GRANT EXEC ON usp_startTrace5 TO [Domain\group]
GRANT INSERT, SELECT, DELETE ON PerfTrace TO [Domain\group]