SQL Server Auditing By using SQL server default trace:

We have a many number of SQL Server instances with versions ranging from 2005 upwards and we want to be alerted about a number of diverse events for auditing.

SQL server 2005 onwards we consists only of 5 trace files of 20MB each, and they get overwritten often, especially in a busy SQL Server environment.

The default traces will capture the below events and you can have the history.

EventID Event_Description

18 Audit Server Starts And Stops20 Audit Login Failed22 ErrorLog46 Object:Created47 Object:Deleted55 Hash Warning69 Sort Warnings79 Missing Column Statistics80 Missing Join Predicate81 Server Memory Change92 Data File Auto Grow93 Log File Auto Grow94 Data File Auto Shrink95 Log File Auto Shrink

102 Audit Database Scope GDR Event103 Audit Schema Object GDR Event104 Audit Addlogin Event105 Audit Login GDR Event106 Audit Login Change Property Event107 Audit Add Login to Server Role Event108 Audit Add DB User Event109 Audit Add Member to DB Role Event110 Audit Add Role Event111 Audit Backup/Restore Event112 Audit DBCC Event113 Audit Change Audit Event152 Audit Change Database Owner153 Audit Schema Object Take Ownership Event155 FT:Crawl Started156 FT:Crawl Stopped164 Object:Altered

We have implemented in our organization to monitor the above events for all our critical production servers.

Prerequisites:

1. One SQL 2012 server with SSIS to host the audit database and SQL job to import the trace to tables.

2. Create the shared folder on Audit server (SQL 2012)

Steps:S.No Task Comment

1 Create the DefaultTraceAudit database on SQL 2012 instance To capture all the audit details

2

Create below tables in DefaultTraceAudit database To capture individual audit tasks1. temp_trc To process the data from Trace file to Temp table2. trc_AlteredObjects Script Attached3. trc_CreatedUsersAndLogins Script Attached4. trc_DroppedUsersAndLogins Script Attached5. trc_ErrorLog Script Attached6. trc_FileGrowAndShrink Script Attached7. trc_FTSearch Script Attached8. trc_LogFileAutoGrowAndShrink Script Attached9. trc_LoginFailed Script Attached10. trc_MemoryChangesEvents Script Attached11. trc_MissingStatsAndPredicates Script Attached12. trc_ServerStarts Script Attached13. trc_SortAndHashWarnings Script Attached

3 Create the F:\Default_Trace_Audit folder on SQL 2012 server To collect the trace files from all SQL servers

4 Identify the default trace file location on source server Script attached

5 Grant Read access to SQL 2012 serviceagent account on trace file for all SQL servers

To copy the trace files from source server to Audit server(SQL 2012)

Copy the SSIS package(ImportDefaultTrace.dtsx) to the SQL 2012 server

SSIS package Attached & scripts also attached which are used in SSIS package

6Import the SSIS package SQL server Data tolls(SQL 2012)

7 Change the Folder path value to where you are copying the Trace file Screen shot attached

8

Change the Folder path in for each editor.(Double click on ForEach Loop Container--Connection--Change the folder path same as above--Click OK)

Screen shot attached

9

Create the job for each SQL server with below steps. Script AttachedStep1. Copy trace files from Source server to SQL 2012 server.Step2. Execute the SSIS packages to process the Trace files to tables.Step3. Delete the Trace files from the SQL 2012 destination folder.

Steps to do the changes in SSIS package:

Change the Folder path value to where you are copying the Trace file

Scripts to Create Tables

USE [DefaultTraceAudit]GO

/****** Object: Table [dbo].[trc_LoginFailed] Script Date: 3/18/2014 2:59:46 PM ******/SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ONGO

CREATE TABLE [dbo].[trc_LoginFailed]([EventName] [nvarchar](128) NULL,[subclass_name] [nvarchar](128) NULL,[DatabaseName] [nvarchar](256) NULL,[DatabaseID] [int] NULL,[NTDomainName] [nvarchar](256) NULL,[ApplicationName] [nvarchar](256) NULL,[LoginName] [nvarchar](256) NULL,[SPID] [int] NULL,[StartTime] [datetime] NULL,[ServerName] [nvarchar](256) NULL,[SessionLoginName] [nvarchar](256) NULL

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[trc_FileGrowAndShrink] Script Date: 3/18/2014 2:59:46 PM ******/SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ONGO

CREATE TABLE [dbo].[trc_FileGrowAndShrink]([EventName] [nvarchar](128) NULL,[ServerName] [nvarchar](256) NULL,[DatabaseName] [nvarchar](256) NULL,[DatabaseID] [int] NULL,[NTDomainName] [nvarchar](256) NULL,[ApplicationName] [nvarchar](256) NULL,[LoginName] [nvarchar](256) NULL,[SPID] [int] NULL,[Duration] [bigint] NULL,[StartTime] [datetime] NULL,[EndTime] [datetime] NULL

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[trc_AlteredObjects] Script Date: 3/18/2014 2:59:46 PM ******/SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ONGO

SET ANSI_PADDING ONGO

CREATE TABLE [dbo].[trc_AlteredObjects]([EventName] [nvarchar](128) NULL,[subclass_name] [nvarchar](128) NULL,[DBName] [nvarchar](128) NULL,[NTDomainName] [nvarchar](256) NULL,[NTUserName] [nvarchar](256) NULL,[HostName] [nvarchar](256) NULL,[ApplicationName] [nvarchar](256) NULL,[LoginName] [nvarchar](256) NULL,[Duration] [bigint] NULL,[StartTime] [datetime] NULL,[ServerName] [nvarchar](256) NULL,[ObjectName] [nvarchar](256) NULL,[ObjectType] [varchar](58) NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFFGO

/****** Object: Table [dbo].[temp_trc] Script Date: 3/18/2014 2:59:46 PM ******/SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[temp_trc]([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,[GroupID] [int] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

/****** Object: Table [dbo].[trc_SortAndHashWarnings] Script Date: 3/18/2014 2:59:46 PM ******/SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ONGO

CREATE TABLE [dbo].[trc_SortAndHashWarnings]([EventName] [nvarchar](128) NULL,[subclass_name] [nvarchar](128) NULL,[DatabaseName] [nvarchar](256) NULL,[DatabaseID] [int] NULL,[NTDomainName] [nvarchar](256) NULL,[ApplicationName] [nvarchar](256) NULL,[LoginName] [nvarchar](256) NULL,[SPID] [int] NULL,[StartTime] [datetime] NULL,[ServerName] [nvarchar](256) NULL

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[trc_MissingStatsAndPredicates] Script Date: 3/18/2014 2:59:46 PM ******/SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ONGO

CREATE TABLE [dbo].[trc_MissingStatsAndPredicates]([EventName] [nvarchar](128) NULL,[DatabaseName] [nvarchar](256) NULL,[DatabaseID] [int] NULL,[NTDomainName] [nvarchar](256) NULL,[ApplicationName] [nvarchar](256) NULL,[LoginName] [nvarchar](256) NULL,[SPID] [int] NULL,[StartTime] [datetime] NULL,[ServerName] [nvarchar](256) NULL

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[trc_LogFileAutoGrowAndShrink] Script Date: 3/18/2014 2:59:46 PM ******/SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ONGO

CREATE TABLE [dbo].[trc_LogFileAutoGrowAndShrink]([EventName] [nvarchar](128) NULL,[DatabaseName] [nvarchar](256) NULL,[DatabaseID] [int] NULL,[NTDomainName] [nvarchar](256) NULL,[ApplicationName] [nvarchar](256) NULL,[LoginName] [nvarchar](256) NULL,[SPID] [int] NULL,[Duration] [bigint] NULL,[StartTime] [datetime] NULL,[ServerName] [nvarchar](256) NULL,[EndTime] [datetime] NULL

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[trc_MemoryChangesEvents] Script Date: 3/18/2014 2:59:46 PM ******/SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ONGO

CREATE TABLE [dbo].[trc_MemoryChangesEvents]([EventName] [nvarchar](128) NULL,[subclass_name] [nvarchar](128) NULL,[IsSystem] [int] NULL,[StartTime] [datetime] NULL,[ServerName] [nvarchar](256) NULL

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[trc_ErrorLog] Script Date: 3/18/2014 2:59:46 PM ******/SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ONGO

CREATE TABLE [dbo].[trc_ErrorLog]([EventName] [nvarchar](128) NULL,[DatabaseName] [nvarchar](256) NULL,[DatabaseID] [int] NULL,[NTDomainName] [nvarchar](256) NULL,[ApplicationName] [nvarchar](256) NULL,

[LoginName] [nvarchar](256) NULL,[SPID] [int] NULL,[StartTime] [datetime] NULL,[ServerName] [nvarchar](256) NULL,[TextData] [ntext] NULL,[Severity] [int] NULL,[Error] [int] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

/****** Object: Table [dbo].[trc_CreatedUsersAndLogins] Script Date: 3/18/2014 2:59:46 PM ******/SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ONGO

CREATE TABLE [dbo].[trc_CreatedUsersAndLogins]([EventName] [nvarchar](128) NULL,[subclass_name] [nvarchar](128) NULL,[DatabaseName] [nvarchar](256) NULL,[DatabaseID] [int] NULL,[NTDomainName] [nvarchar](256) NULL,[ApplicationName] [nvarchar](256) NULL,[LoginName] [nvarchar](256) NULL,[SPID] [int] NULL,[StartTime] [datetime] NULL,[ServerName] [nvarchar](256) NULL,[RoleName] [nvarchar](256) NULL,[TargetUserName] [nvarchar](256) NULL,[TargetLoginName] [nvarchar](256) NULL,[SessionLoginName] [nvarchar](256) NULL

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[trc_DroppedUsersAndLogins] Script Date: 3/18/2014 2:59:46 PM ******/SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ONGO

CREATE TABLE [dbo].[trc_DroppedUsersAndLogins]([EventName] [nvarchar](128) NULL,[subclass_name] [nvarchar](128) NULL,[DatabaseName] [nvarchar](256) NULL,[DatabaseID] [int] NULL,[NTDomainName] [nvarchar](256) NULL,[ApplicationName] [nvarchar](256) NULL,[LoginName] [nvarchar](256) NULL,[SPID] [int] NULL,[StartTime] [datetime] NULL,[ServerName] [nvarchar](256) NULL,[RoleName] [nvarchar](256) NULL,

[TargetUserName] [nvarchar](256) NULL,[TargetLoginName] [nvarchar](256) NULL,[SessionLoginName] [nvarchar](256) NULL

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[trc_ServerStarts] Script Date: 3/18/2014 2:59:46 PM ******/SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ONGO

CREATE TABLE [dbo].[trc_ServerStarts]([EventName] [nvarchar](128) NULL,[subclass_name] [nvarchar](128) NULL,[DatabaseName] [nvarchar](256) NULL,[DatabaseID] [int] NULL,[NTDomainName] [nvarchar](256) NULL,[ApplicationName] [nvarchar](256) NULL,[LoginName] [nvarchar](256) NULL,[SPID] [int] NULL,[StartTime] [datetime] NULL,[ServerName] [nvarchar](256) NULL,[SessionLoginName] [nvarchar](256) NULL

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[trc_FTSearch] Script Date: 3/18/2014 2:59:46 PM ******/SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ONGO

CREATE TABLE [dbo].[trc_FTSearch]([EventName] [nvarchar](128) NULL,[DatabaseName] [nvarchar](128) NULL,[DatabaseID] [int] NULL,[NTDomainName] [nvarchar](256) NULL,[ApplicationName] [nvarchar](256) NULL,[LoginName] [nvarchar](256) NULL,[SPID] [int] NULL,[StartTime] [datetime] NULL,[ServerName] [nvarchar](256) NULL,[IsSystem] [int] NULL

) ON [PRIMARY]

GO

Identify the default trace file location on source server:

SELECT * FROM ::fn_trace_getinfo(default)

SSIS package(ImportDefaultTrace.dtsx)

Scripts used in SSIS package:

-- Merge Data Auto Grow and Auto ShrinkMERGE trc_FileGrowAndShrink AS target USING ( SELECT TE.name AS [EventName] , T.ServerName , T.DatabaseName , t.DatabaseID , t.NTDomainName , t.ApplicationName , t.LoginName , t.SPID , t.Duration , t.StartTime , t.EndTime FROM [dbo].[temp_trc] T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id WHERE te.name = 'Data File Auto Grow' OR te.name = 'Data File Auto Shrink' ) AS source ON target.StartTime = source.StartTime AND target.ServerName = source.ServerName WHEN NOT MATCHED BY TARGET THEN

INSERT ( [EventName] , ServerName , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , Duration , StartTime , EndTime ) VALUES ( [EventName] , ServerName , DatabaseName , DatabaseID , NTDomainName ,

ApplicationName , LoginName , SPID , Duration , StartTime , EndTime ); -- Merge Log Autogrow and Autoshrink MERGE trc_LogFileAutoGrowAndShrink AS target USING ( SELECT TE.name AS [EventName] , T.DatabaseName , t.DatabaseID , t.NTDomainName , t.ApplicationName , t.LoginName , t.SPID , t.Duration , t.StartTime , T.ServerName , t.EndTime FROM [dbo].[temp_trc] T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id WHERE te.name = 'Log File Auto Grow' OR te.name = 'Log File Auto Shrink' ) AS source ON target.StartTime = source.StartTime AND target.ServerName = source.ServerName WHEN NOT MATCHED BY TARGET THEN

INSERT ( EventName , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , Duration , StartTime , ServerName , EndTime ) VALUES ( EventName , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , Duration , StartTime ,

ServerName , EndTime );  -- Merge Error Log MERGE trc_ErrorLog AS target USING ( SELECT TE.name AS [EventName] , T.DatabaseName , t.DatabaseID , t.NTDomainName , t.ApplicationName , t.LoginName , t.SPID , t.StartTime , T.ServerName , t.TextData , t.Severity , t.Error FROM [dbo].[temp_trc] T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id WHERE te.name = 'ErrorLog' ) AS source ON target.StartTime = source.StartTime AND target.ServerName = source.ServerName WHEN NOT MATCHED BY TARGET THEN

INSERT ( EventName , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , StartTime , ServerName , TextData , Severity , Error

) VALUES ( EventName , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , StartTime , ServerName , TextData , Severity ,

Error );

 -- Merge Sort and Hash Warnings MERGE trc_SortAndHashWarnings AS target USING ( SELECT TE.name AS [EventName] , v.subclass_name , T.DatabaseName , t.DatabaseID , t.NTDomainName , t.ApplicationName , t.LoginName , t.SPID , t.StartTime , T.ServerName FROM [dbo].[temp_trc] T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id AND v.subclass_value = t.EventSubClass WHERE te.name = 'Hash Warning' OR te.name = 'Sort Warnings' ) AS source ON target.StartTime = source.StartTime AND target.ServerName = source.ServerName WHEN NOT MATCHED BY TARGET THEN

INSERT ( EventName , subclass_name , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , StartTime , ServerName ) VALUES ( EventName , subclass_name , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , StartTime , ServerName

); 

-- Merge Missing Stats And Predicates MERGE trc_MissingStatsAndPredicates AS target USING ( SELECT TE.name AS [EventName] , T.DatabaseName , t.DatabaseID , t.NTDomainName , t.ApplicationName , t.LoginName , t.SPID , t.StartTime , T.ServerName FROM [dbo].[temp_trc] T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id WHERE te.name = 'Missing Column Statistics' OR te.name = 'Missing Join Predicate' ) AS source ON target.StartTime = source.StartTime AND target.ServerName = source.ServerName WHEN NOT MATCHED BY TARGET THEN

INSERT ( EventName , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , StartTime , ServerName ) VALUES ( EventName , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , StartTime , ServerName

); -- Merge FT Search Events MERGE trc_FTSearch AS target USING ( SELECT TE.name AS [EventName] , DB_NAME(t.DatabaseID) AS DatabaseName , t.DatabaseID , t.NTDomainName , t.ApplicationName , t.LoginName ,

t.SPID , t.StartTime , T.ServerName , t.IsSystem FROM [dbo].[temp_trc] T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id WHERE te.name = 'FT:Crawl Started' OR te.name = 'FT:Crawl Aborted' OR te.name = 'FT:Crawl Stopped' ) AS source ON target.StartTime = source.StartTime AND target.ServerName = source.ServerName WHEN NOT MATCHED BY TARGET THEN

INSERT ( EventName , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , StartTime , ServerName , IsSystem

) VALUES ( EventName , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , StartTime , ServerName , IsSystem

); -- Merge Altered Objects MERGE trc_AlteredObjects AS target USING ( SELECT TE.name AS [EventName] , v.subclass_name , DB_NAME(t.DatabaseId) AS DBName , T.NTDomainName , t.NTUserName , t.HostName , t.ApplicationName , t.LoginName , t.Duration , t.StartTime , T.ServerName , t.ObjectName ,

CASE t.ObjectType WHEN 8259 THEN 'Check Constraint' WHEN 8260 THEN 'Default (constraint or standalone)' WHEN 8262 THEN 'Foreign-key Constraint' WHEN 8272 THEN 'Stored Procedure' WHEN 8274 THEN 'Rule' WHEN 8275 THEN 'System Table' WHEN 8276 THEN 'Trigger on Server' WHEN 8277 THEN '(User-defined) Table' WHEN 8278 THEN 'View' WHEN 8280 THEN 'Extended Stored Procedure' WHEN 16724 THEN 'CLR Trigger' WHEN 16964 THEN 'Database' WHEN 16975 THEN 'Object' WHEN 17222 THEN 'FullText Catalog' WHEN 17232 THEN 'CLR Stored Procedure' WHEN 17235 THEN 'Schema' WHEN 17475 THEN 'Credential' WHEN 17491 THEN 'DDL Event' WHEN 17741 THEN 'Management Event' WHEN 17747 THEN 'Security Event' WHEN 17749 THEN 'User Event' WHEN 17985 THEN 'CLR Aggregate Function' WHEN 17993 THEN 'Inline Table-valued SQL Function' WHEN 18000 THEN 'Partition Function' WHEN 18002 THEN 'Replication Filter Procedure' WHEN 18004 THEN 'Table-valued SQL Function' WHEN 18259 THEN 'Server Role' WHEN 18263 THEN 'Microsoft Windows Group' WHEN 19265 THEN 'Asymmetric Key' WHEN 19277 THEN 'Master Key' WHEN 19280 THEN 'Primary Key' WHEN 19283 THEN 'ObfusKey' WHEN 19521 THEN 'Asymmetric Key Login' WHEN 19523 THEN 'Certificate Login' WHEN 19538 THEN 'Role' WHEN 19539 THEN 'SQL Login' WHEN 19543 THEN 'Windows Login' WHEN 20034 THEN 'Remote Service Binding' WHEN 20036 THEN 'Event Notification on Database' WHEN 20037 THEN 'Event Notification' WHEN 20038 THEN 'Scalar SQL Function' WHEN 20047 THEN 'Event Notification on Object' WHEN 20051 THEN 'Synonym' WHEN 20549 THEN 'End Point' WHEN 20801 THEN 'Adhoc Queries which may be cached' WHEN 20816 THEN 'Prepared Queries which may be cached' WHEN 20819 THEN 'Service Broker Service Queue' WHEN 20821 THEN 'Unique Constraint' WHEN 21057 THEN 'Application Role' WHEN 21059 THEN 'Certificate' WHEN 21075 THEN 'Server' WHEN 21076 THEN 'Transact-SQL Trigger' WHEN 21313 THEN 'Assembly' WHEN 21318 THEN 'CLR Scalar Function'

WHEN 21321 THEN 'Inline scalar SQL Function' WHEN 21328 THEN 'Partition Scheme' WHEN 21333 THEN 'User' WHEN 21571 THEN 'Service Broker Service Contract' WHEN 21572 THEN 'Trigger on Database' WHEN 21574 THEN 'CLR Table-valued Function' WHEN 21577 THEN 'Internal Table (For example, XML Node Table, Queue Table.)' WHEN 21581 THEN 'Service Broker Message Type' WHEN 21586 THEN 'Service Broker Route' WHEN 21587 THEN 'Statistics' WHEN 21825 THEN 'User' WHEN 21827 THEN 'User' WHEN 21831 THEN 'User' WHEN 21843 THEN 'User' WHEN 21847 THEN 'User' WHEN 22099 THEN 'Service Broker Service' WHEN 22601 THEN 'Index' WHEN 22604 THEN 'Certificate Login' WHEN 22611 THEN 'XMLSchema' WHEN 22868 THEN 'Type' ELSE 'Hmmm???' END AS ObjectType FROM [dbo].[temp_trc] T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id AND v.subclass_value = t.EventSubClass WHERE TE.name IN ( 'Object:Created', 'Object:Deleted', 'Object:Altered' )  -- filter statistics created by SQL server AND t.ObjectType NOT IN ( 21587 )  -- filter tempdb objects AND DatabaseID <> 2  -- get only events in the past 24 hours ) AS source ON target.StartTime = source.StartTime AND target.ServerName = source.ServerName WHEN NOT MATCHED BY TARGET THEN

INSERT ( EventName , subclass_name , DBName , NTDomainName , NTUserName , HostName , ApplicationName ,

LoginName , Duration , StartTime , ServerName , ObjectName , ObjectType

) VALUES ( EventName , subclass_name , DBName , NTDomainName , NTUserName , HostName , ApplicationName , LoginName , Duration , StartTime , ServerName , ObjectName , ObjectType

); -- Merge Created Users And Logins MERGE trc_CreatedUsersAndLogins AS target USING ( SELECT TE.name AS [EventName] , v.subclass_name , T.DatabaseName , t.DatabaseID , t.NTDomainName , t.ApplicationName , t.LoginName , t.SPID , t.StartTime , T.ServerName , t.RoleName , t.TargetUserName , t.TargetLoginName , t.SessionLoginName FROM [dbo].[temp_trc] T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id AND v.subclass_value = t.EventSubClass WHERE te.name IN ( 'Audit Addlogin Event', 'Audit Add DB User Event', 'Audit Add Member to DB Role Event' ) AND v.subclass_name IN ( 'add', 'Grant database access' ) ) AS source ON target.StartTime = source.StartTime AND target.ServerName = source.ServerName WHEN NOT MATCHED BY TARGET

THENINSERT (

EventName , subclass_name , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , StartTime , ServerName , RoleName , TargetUserName , TargetLoginName , SessionLoginName ) VALUES ( EventName , subclass_name , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , StartTime , ServerName , RoleName , TargetUserName , TargetLoginName , SessionLoginName

); -- Merge Dropped Users And Logins MERGE trc_DroppedUsersAndLogins AS target USING ( SELECT TE.name AS [EventName] , v.subclass_name , T.DatabaseName , t.DatabaseID , t.NTDomainName , t.ApplicationName , t.LoginName , t.SPID , t.StartTime , T.ServerName , t.RoleName , t.TargetUserName , t.TargetLoginName , t.SessionLoginName FROM [dbo].[temp_trc] T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id

JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id AND v.subclass_value = t.EventSubClass WHERE te.name IN ( 'Audit Addlogin Event', 'Audit Add DB User Event', 'Audit Add Member to DB Role Event' ) AND v.subclass_name IN ( 'Drop', 'Revoke database access' ) ) AS source ON target.StartTime = source.StartTime AND target.ServerName = source.ServerName WHEN NOT MATCHED BY TARGET THEN

INSERT ( EventName , subclass_name , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , StartTime , ServerName , RoleName , TargetUserName , TargetLoginName , SessionLoginName ) VALUES ( EventName , subclass_name , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , StartTime , ServerName , RoleName , TargetUserName , TargetLoginName , SessionLoginName

); -- Merge Login Failed Events MERGE trc_LoginFailed AS target USING ( SELECT TE.name AS [EventName] , v.subclass_name , T.DatabaseName , t.DatabaseID , t.NTDomainName ,

t.ApplicationName , t.LoginName , t.SPID , t.StartTime , T.ServerName , t.SessionLoginName FROM [dbo].[temp_trc] T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id AND v.subclass_value = t.EventSubClass WHERE te.name IN ( 'Audit Login Failed' ) ) AS source ON target.StartTime = source.StartTime AND target.ServerName = source.ServerName WHEN NOT MATCHED BY TARGET THEN

INSERT ( EventName , subclass_name , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , StartTime , ServerName , SessionLoginName ) VALUES ( EventName , subclass_name , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , StartTime , ServerName , SessionLoginName

); -- Merge Server Start events MERGE trc_ServerStarts AS target USING ( SELECT TE.name AS [EventName] , v.subclass_name , T.DatabaseName , t.DatabaseID , t.NTDomainName , t.ApplicationName ,

t.LoginName , t.SPID , t.StartTime , T.ServerName , t.SessionLoginName FROM [dbo].[temp_trc] T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id AND v.subclass_value = t.EventSubClass WHERE te.name IN ( 'Audit Server Starts and Stops' ) ) AS source ON target.StartTime = source.StartTime AND target.ServerName = source.ServerName WHEN NOT MATCHED BY TARGET THEN

INSERT ( EventName , subclass_name , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , StartTime , ServerName , SessionLoginName ) VALUES ( EventName , subclass_name , DatabaseName , DatabaseID , NTDomainName , ApplicationName , LoginName , SPID , StartTime , ServerName , SessionLoginName

); -- Merge Memory Change Events MERGE trc_MemoryChangesEvents AS target USING ( SELECT TE.name AS [EventName] , v.subclass_name , t.IsSystem , T.StartTime , T.ServerName FROM [dbo].[temp_trc] T

JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id AND v.subclass_value = t.EventSubClass WHERE te.name IN ( 'Server Memory Change' ) ) AS source ON target.StartTime = source.StartTime AND target.ServerName = source.ServerName WHEN NOT MATCHED BY TARGET THEN

INSERT ( EventName , subclass_name , IsSystem , StartTime , ServerName

) VALUES ( EventName , subclass_name , IsSystem , StartTime , ServerName

);    Script to create job for each SQL server:

USE [msdb]GO

/****** Object: Job [JobName] Script Date: 3/18/2014 3:06:12 PM ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 3/18/2014 3:06:12 PM ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N' JobName ',

@enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0,

@delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback/****** Object: Step [copy] Script Date: 3/18/2014 3:06:12 PM ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'copy',

@step_id=1, @cmdexec_success_code=0, @on_success_action=4, @on_success_step_id=2, @on_fail_action=3, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'CmdExec', @command=N'copy \\Source server anme\Log\*.trc \\Destination server

anme\Default_Trace_Audit\Trace\', @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback/****** Object: Step [SSIS] Script Date: 3/18/2014 3:06:12 PM ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SSIS',

@step_id=2, @cmdexec_success_code=0, @on_success_action=4, @on_success_step_id=3, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'SSIS', @command=N'/FILE "\"F:\Default_Trace_Audit\Packages\

ImportDefaultTrace.dtsx\"" /CHECKPOINTING OFF /REPORTING E', @database_name=N'master', @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback/****** Object: Step [CB2-IVU-DWSQL_Deletefiles] Script Date: 3/18/2014 3:06:12 PM ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CB2-IVU-DWSQL_Deletefiles',

@step_id=3, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'CmdExec', @command=N'del F:\Default_Trace_Audit\Trace\*.trc', @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'JobName',

@enabled=1, @freq_type=4, @freq_interval=1,

@freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20140221, @active_end_date=99991231, @active_start_time=20000, @active_end_time=235959, @schedule_uid=N'93e46998-3c10-4400-8f18-9fac578090c4'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GO