SupportSQL

This is a part in a series of “HOMEGROWN COMPLIANCE” articles dealing with various aspects of meeting compliance related goals in the current general corporate compliance environment (ISO, SOX, ISA, CPI, etc.)

This installment provides the stored procedure and description how to collect the data from the default trace that runs, you guessed it, by default in SQL Server 2005. Also included is a view to join to event descriptions for key fields. What is nice is that additional traces, hindering performance, are not needed for this approach as the default trace is usually already consuming resources. This method provides a way to utilize that trace data.

The default trace data collects many important SQL Server events that occur in your environment including security and DDL audit data as well as performance data and optimization warnings and much more. This data once identified and reported is usually sufficient to satisfy most compliance implementations as the data is highly granular and the collection method is secure, auditable and reliable.

The proc will create the table in the current database if it does not exists along with a clustered index. Then the proc reads the trace data into the table keeping track of the sequence number as to not overlap and create record duplication. The issue is that if not run often enough it may lose some data as the trace files cycle by default on five fairly small files. If you run the proc in a job every hour you should be ok, and you could probably drop that to once a night depending on the traffic levels in your environment. The defaults for the default trace can be altered as well, see BOL for more on the Default Trace.

sample data excerpt:

So here is the proc to create and schedule:

CREATE PROCEDURE [dbo].[gp_DBA_DefaultTraceLog]
as
—gets and stores the default trace records
—for change management / compliance /security auditing

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @maxEvent bigint
DECLARE @fn varchar(300)

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[DefaultTrace]’) AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[DefaultTrace](
    [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] NOT 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]
END

/****** Object:  Index [IX_DefaultTrace_clustered]    Script Date: 04/17/2009 09:08:49 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N’[dbo].[DefaultTrace]’) AND name = N’IX_DefaultTrace_clustered’)
CREATE CLUSTERED INDEX [IX_DefaultTrace_clustered] ON [dbo].[DefaultTrace]
(
    [EventSequence] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

SELECT @maxEvent = max(eventsequence) FROM dbo.DefaultTrace dt
SELECT @fn = CONVERT(varchar(300),[value]) FROM :: fn_trace_getinfo(1) WHERE [property] = 2
INSERT INTO dbo.DefaultTrace
SELECT * FROM msdb.sys.fn_trace_gettable(@fn, DEFAULT) t WHERE t.EventSequence IS NOT NULL AND t.EventSequence > isnull(@maxEvent,0)

Here is a view that joins to the normalized tables for event descriptions. Notice I use TOP 1000 as this table has millions of rows in it by the time the archive cycle runs (so this only retrieves a sampling of the data.) Indexing strategy should be applied according to your specific query needs against the data. The archiving is not included here, but the view is this query:

SELECT TOP 1000
    tc.NAME AS TraceCategoryName
    ,te.NAME AS TraceEventName
    ,tsv.subclass_name
    ,dt.EventSequence
    ,dt.xactsequence
    ,dt.ObjectName
    ,dt.ObjectType
    ,dt.ObjectID
    ,dt.ObjectID2
    ,dt.DatabaseName
    ,dt.LoginName
    ,dt.HostName
    ,dt.ClientProcessID
    ,dt.ApplicationName
    ,dt.StartTime
    ,dt.IndexID
    ,dt.error 
FROM
    DefaultTrace dt WITH (NOLOCK)
JOIN sys.trace_events te WITH (NOLOCK) ON dt.EventClass = te.trace_event_id
JOIN sys.trace_categories tc WITH (NOLOCK) ON tc.category_id = te.category_id
JOIN sys.trace_subclass_values tsv WITH (NOLOCK) ON dt.EventSubClass = tsv.subclass_value AND dt.EventClass = tsv.trace_event_id
WHERE databasename NOT IN (‘tempdb’)

—by Alek Kirstein – SupportSQL.com