Перейти к содержимому

Аудит mssql 2005 с помощью SQL Server Profiler и создания задания в агенте.

Задание.

Не обходим аудит который отслеживал кто изменил , создал, удалил, задание или базу, а также кто менял настройки mssql.

Был выбран Profiler так как в нем уже есть шаблоны аудита.

Для на чала создаем трассировку в самом Profiler.

Аудит mssql 2005 с помощью SQL Server Profiler и создания задания в агенте.

Потом делаем определение трассировки

Аудит mssql 2005 с помощью SQL Server Profiler и создания задания в агенте.

Получаем вот такой код

-- ======================================================================================================

-- Author: Константин Москвичёв

-- Create date: 13/04/2015

-- Description:    0.2b

-- Описание: Запускает трасировку для аудита. 

-- ======================================================================================================

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @filename nvarchar(128)
declare @filecount int

--указываем максимальный размер файла
set @maxfilesize = 99

--указываем путь и имя файла, к имени файла дописывается текушия дата и время

set @filename = '\\192.168.0.38\Backup\audit_rx6600\TracE'+CONVERT(CHAR(20),GETDATE(),112)+REPLACE(CONVERT(CHAR(20),GETDATE(),108),':','')

-- НАстройка параметров трасировки
exec	@rc = sp_trace_create
	@traceid = @TraceID output, 
	@options = 2, --опция 2 https://msdn.microsoft.com/ru-ru/library/ms190362.aspx
	@tracefile = @filename, --имя трасировки
	@maxfilesize = @maxfilesize, -- максимальный размер трасировки
	@stoptime = NULL, -- не ограниченое время выполнения
	@filecount =  50 -- не больше 50 файлов хранить
	
if (@rc != 0) goto error


-- Сюда можно добавить любые параметры трасировки из sql Profile. 
-- В данный момент здась применен шаблон audit с фильтром 
-- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 109, 7, @on
exec sp_trace_setevent @TraceID, 109, 23, @on
exec sp_trace_setevent @TraceID, 109, 39, @on
exec sp_trace_setevent @TraceID, 109, 8, @on
exec sp_trace_setevent @TraceID, 109, 40, @on
exec sp_trace_setevent @TraceID, 109, 64, @on
exec sp_trace_setevent @TraceID, 109, 9, @on
exec sp_trace_setevent @TraceID, 109, 41, @on
exec sp_trace_setevent @TraceID, 109, 49, @on
exec sp_trace_setevent @TraceID, 109, 10, @on
exec sp_trace_setevent @TraceID, 109, 26, @on
exec sp_trace_setevent @TraceID, 109, 42, @on
exec sp_trace_setevent @TraceID, 109, 50, @on
exec sp_trace_setevent @TraceID, 109, 3, @on
exec sp_trace_setevent @TraceID, 109, 11, @on
exec sp_trace_setevent @TraceID, 109, 35, @on
exec sp_trace_setevent @TraceID, 109, 43, @on
exec sp_trace_setevent @TraceID, 109, 51, @on
exec sp_trace_setevent @TraceID, 109, 4, @on
exec sp_trace_setevent @TraceID, 109, 12, @on
exec sp_trace_setevent @TraceID, 109, 28, @on
exec sp_trace_setevent @TraceID, 109, 44, @on
exec sp_trace_setevent @TraceID, 109, 60, @on
exec sp_trace_setevent @TraceID, 109, 21, @on
exec sp_trace_setevent @TraceID, 109, 29, @on
exec sp_trace_setevent @TraceID, 109, 37, @on
exec sp_trace_setevent @TraceID, 109, 6, @on
exec sp_trace_setevent @TraceID, 109, 14, @on
exec sp_trace_setevent @TraceID, 109, 38, @on
exec sp_trace_setevent @TraceID, 111, 7, @on
exec sp_trace_setevent @TraceID, 111, 23, @on
exec sp_trace_setevent @TraceID, 111, 8, @on
exec sp_trace_setevent @TraceID, 111, 40, @on
exec sp_trace_setevent @TraceID, 111, 64, @on
exec sp_trace_setevent @TraceID, 111, 9, @on
exec sp_trace_setevent @TraceID, 111, 41, @on
exec sp_trace_setevent @TraceID, 111, 49, @on
exec sp_trace_setevent @TraceID, 111, 6, @on
exec sp_trace_setevent @TraceID, 111, 10, @on
exec sp_trace_setevent @TraceID, 111, 14, @on
exec sp_trace_setevent @TraceID, 111, 26, @on
exec sp_trace_setevent @TraceID, 111, 38, @on
exec sp_trace_setevent @TraceID, 111, 50, @on
exec sp_trace_setevent @TraceID, 111, 3, @on
exec sp_trace_setevent @TraceID, 111, 11, @on
exec sp_trace_setevent @TraceID, 111, 35, @on
exec sp_trace_setevent @TraceID, 111, 51, @on
exec sp_trace_setevent @TraceID, 111, 4, @on
exec sp_trace_setevent @TraceID, 111, 12, @on
exec sp_trace_setevent @TraceID, 111, 28, @on
exec sp_trace_setevent @TraceID, 111, 60, @on
exec sp_trace_setevent @TraceID, 111, 21, @on
exec sp_trace_setevent @TraceID, 111, 29, @on
exec sp_trace_setevent @TraceID, 104, 7, @on
exec sp_trace_setevent @TraceID, 104, 23, @on
exec sp_trace_setevent @TraceID, 104, 8, @on
exec sp_trace_setevent @TraceID, 104, 40, @on
exec sp_trace_setevent @TraceID, 104, 64, @on
exec sp_trace_setevent @TraceID, 104, 9, @on
exec sp_trace_setevent @TraceID, 104, 41, @on
exec sp_trace_setevent @TraceID, 104, 49, @on
exec sp_trace_setevent @TraceID, 104, 6, @on
exec sp_trace_setevent @TraceID, 104, 10, @on
exec sp_trace_setevent @TraceID, 104, 14, @on
exec sp_trace_setevent @TraceID, 104, 26, @on
exec sp_trace_setevent @TraceID, 104, 42, @on
exec sp_trace_setevent @TraceID, 104, 50, @on
exec sp_trace_setevent @TraceID, 104, 3, @on
exec sp_trace_setevent @TraceID, 104, 11, @on
exec sp_trace_setevent @TraceID, 104, 35, @on
exec sp_trace_setevent @TraceID, 104, 43, @on
exec sp_trace_setevent @TraceID, 104, 51, @on
exec sp_trace_setevent @TraceID, 104, 4, @on
exec sp_trace_setevent @TraceID, 104, 12, @on
exec sp_trace_setevent @TraceID, 104, 28, @on
exec sp_trace_setevent @TraceID, 104, 60, @on
exec sp_trace_setevent @TraceID, 104, 5, @on
exec sp_trace_setevent @TraceID, 104, 21, @on
exec sp_trace_setevent @TraceID, 128, 7, @on
exec sp_trace_setevent @TraceID, 128, 23, @on
exec sp_trace_setevent @TraceID, 128, 8, @on
exec sp_trace_setevent @TraceID, 128, 40, @on
exec sp_trace_setevent @TraceID, 128, 64, @on
exec sp_trace_setevent @TraceID, 128, 1, @on
exec sp_trace_setevent @TraceID, 128, 41, @on
exec sp_trace_setevent @TraceID, 128, 49, @on
exec sp_trace_setevent @TraceID, 128, 6, @on
exec sp_trace_setevent @TraceID, 128, 10, @on
exec sp_trace_setevent @TraceID, 128, 14, @on
exec sp_trace_setevent @TraceID, 128, 26, @on
exec sp_trace_setevent @TraceID, 128, 34, @on
exec sp_trace_setevent @TraceID, 128, 50, @on
exec sp_trace_setevent @TraceID, 128, 3, @on
exec sp_trace_setevent @TraceID, 128, 11, @on
exec sp_trace_setevent @TraceID, 128, 35, @on
exec sp_trace_setevent @TraceID, 128, 51, @on
exec sp_trace_setevent @TraceID, 128, 4, @on
exec sp_trace_setevent @TraceID, 128, 12, @on
exec sp_trace_setevent @TraceID, 128, 28, @on
exec sp_trace_setevent @TraceID, 128, 60, @on
exec sp_trace_setevent @TraceID, 128, 21, @on
exec sp_trace_setevent @TraceID, 128, 29, @on
exec sp_trace_setevent @TraceID, 128, 37, @on
exec sp_trace_setevent @TraceID, 130, 7, @on
exec sp_trace_setevent @TraceID, 130, 23, @on
exec sp_trace_setevent @TraceID, 130, 39, @on
exec sp_trace_setevent @TraceID, 130, 8, @on
exec sp_trace_setevent @TraceID, 130, 40, @on
exec sp_trace_setevent @TraceID, 130, 64, @on
exec sp_trace_setevent @TraceID, 130, 1, @on
exec sp_trace_setevent @TraceID, 130, 41, @on
exec sp_trace_setevent @TraceID, 130, 49, @on
exec sp_trace_setevent @TraceID, 130, 6, @on
exec sp_trace_setevent @TraceID, 130, 10, @on
exec sp_trace_setevent @TraceID, 130, 14, @on
exec sp_trace_setevent @TraceID, 130, 26, @on
exec sp_trace_setevent @TraceID, 130, 34, @on
exec sp_trace_setevent @TraceID, 130, 42, @on
exec sp_trace_setevent @TraceID, 130, 50, @on
exec sp_trace_setevent @TraceID, 130, 3, @on
exec sp_trace_setevent @TraceID, 130, 11, @on
exec sp_trace_setevent @TraceID, 130, 35, @on
exec sp_trace_setevent @TraceID, 130, 43, @on
exec sp_trace_setevent @TraceID, 130, 51, @on
exec sp_trace_setevent @TraceID, 130, 4, @on
exec sp_trace_setevent @TraceID, 130, 12, @on
exec sp_trace_setevent @TraceID, 130, 28, @on
exec sp_trace_setevent @TraceID, 130, 60, @on
exec sp_trace_setevent @TraceID, 130, 21, @on
exec sp_trace_setevent @TraceID, 130, 29, @on
exec sp_trace_setevent @TraceID, 130, 37, @on
exec sp_trace_setevent @TraceID, 107, 7, @on
exec sp_trace_setevent @TraceID, 107, 23, @on
exec sp_trace_setevent @TraceID, 107, 8, @on
exec sp_trace_setevent @TraceID, 107, 40, @on
exec sp_trace_setevent @TraceID, 107, 64, @on
exec sp_trace_setevent @TraceID, 107, 1, @on
exec sp_trace_setevent @TraceID, 107, 9, @on
exec sp_trace_setevent @TraceID, 107, 41, @on
exec sp_trace_setevent @TraceID, 107, 49, @on
exec sp_trace_setevent @TraceID, 107, 10, @on
exec sp_trace_setevent @TraceID, 107, 26, @on
exec sp_trace_setevent @TraceID, 107, 34, @on
exec sp_trace_setevent @TraceID, 107, 42, @on
exec sp_trace_setevent @TraceID, 107, 50, @on
exec sp_trace_setevent @TraceID, 107, 3, @on
exec sp_trace_setevent @TraceID, 107, 11, @on
exec sp_trace_setevent @TraceID, 107, 35, @on
exec sp_trace_setevent @TraceID, 107, 43, @on
exec sp_trace_setevent @TraceID, 107, 51, @on
exec sp_trace_setevent @TraceID, 107, 4, @on
exec sp_trace_setevent @TraceID, 107, 12, @on
exec sp_trace_setevent @TraceID, 107, 28, @on
exec sp_trace_setevent @TraceID, 107, 60, @on
exec sp_trace_setevent @TraceID, 107, 21, @on
exec sp_trace_setevent @TraceID, 107, 29, @on
exec sp_trace_setevent @TraceID, 107, 37, @on
exec sp_trace_setevent @TraceID, 107, 6, @on
exec sp_trace_setevent @TraceID, 107, 14, @on
exec sp_trace_setevent @TraceID, 118, 7, @on
exec sp_trace_setevent @TraceID, 118, 23, @on
exec sp_trace_setevent @TraceID, 118, 8, @on
exec sp_trace_setevent @TraceID, 118, 40, @on
exec sp_trace_setevent @TraceID, 118, 64, @on
exec sp_trace_setevent @TraceID, 118, 1, @on
exec sp_trace_setevent @TraceID, 118, 9, @on
exec sp_trace_setevent @TraceID, 118, 41, @on
exec sp_trace_setevent @TraceID, 118, 49, @on
exec sp_trace_setevent @TraceID, 118, 6, @on
exec sp_trace_setevent @TraceID, 118, 10, @on
exec sp_trace_setevent @TraceID, 118, 14, @on
exec sp_trace_setevent @TraceID, 118, 26, @on
exec sp_trace_setevent @TraceID, 118, 34, @on
exec sp_trace_setevent @TraceID, 118, 50, @on
exec sp_trace_setevent @TraceID, 118, 3, @on
exec sp_trace_setevent @TraceID, 118, 11, @on
exec sp_trace_setevent @TraceID, 118, 35, @on
exec sp_trace_setevent @TraceID, 118, 51, @on
exec sp_trace_setevent @TraceID, 118, 4, @on
exec sp_trace_setevent @TraceID, 118, 12, @on
exec sp_trace_setevent @TraceID, 118, 28, @on
exec sp_trace_setevent @TraceID, 118, 60, @on
exec sp_trace_setevent @TraceID, 118, 5, @on
exec sp_trace_setevent @TraceID, 118, 21, @on
exec sp_trace_setevent @TraceID, 118, 29, @on
exec sp_trace_setevent @TraceID, 118, 37, @on
exec sp_trace_setevent @TraceID, 114, 7, @on
exec sp_trace_setevent @TraceID, 114, 23, @on
exec sp_trace_setevent @TraceID, 114, 8, @on
exec sp_trace_setevent @TraceID, 114, 40, @on
exec sp_trace_setevent @TraceID, 114, 64, @on
exec sp_trace_setevent @TraceID, 114, 1, @on
exec sp_trace_setevent @TraceID, 114, 9, @on
exec sp_trace_setevent @TraceID, 114, 41, @on
exec sp_trace_setevent @TraceID, 114, 49, @on
exec sp_trace_setevent @TraceID, 114, 2, @on
exec sp_trace_setevent @TraceID, 114, 10, @on
exec sp_trace_setevent @TraceID, 114, 26, @on
exec sp_trace_setevent @TraceID, 114, 34, @on
exec sp_trace_setevent @TraceID, 114, 50, @on
exec sp_trace_setevent @TraceID, 114, 3, @on
exec sp_trace_setevent @TraceID, 114, 11, @on
exec sp_trace_setevent @TraceID, 114, 19, @on
exec sp_trace_setevent @TraceID, 114, 35, @on
exec sp_trace_setevent @TraceID, 114, 51, @on
exec sp_trace_setevent @TraceID, 114, 59, @on
exec sp_trace_setevent @TraceID, 114, 4, @on
exec sp_trace_setevent @TraceID, 114, 12, @on
exec sp_trace_setevent @TraceID, 114, 28, @on
exec sp_trace_setevent @TraceID, 114, 44, @on
exec sp_trace_setevent @TraceID, 114, 60, @on
exec sp_trace_setevent @TraceID, 114, 5, @on
exec sp_trace_setevent @TraceID, 114, 21, @on
exec sp_trace_setevent @TraceID, 114, 29, @on
exec sp_trace_setevent @TraceID, 114, 37, @on
exec sp_trace_setevent @TraceID, 114, 6, @on
exec sp_trace_setevent @TraceID, 114, 14, @on
exec sp_trace_setevent @TraceID, 176, 7, @on
exec sp_trace_setevent @TraceID, 176, 23, @on
exec sp_trace_setevent @TraceID, 176, 8, @on
exec sp_trace_setevent @TraceID, 176, 40, @on
exec sp_trace_setevent @TraceID, 176, 64, @on
exec sp_trace_setevent @TraceID, 176, 1, @on
exec sp_trace_setevent @TraceID, 176, 9, @on
exec sp_trace_setevent @TraceID, 176, 41, @on
exec sp_trace_setevent @TraceID, 176, 49, @on
exec sp_trace_setevent @TraceID, 176, 10, @on
exec sp_trace_setevent @TraceID, 176, 26, @on
exec sp_trace_setevent @TraceID, 176, 34, @on
exec sp_trace_setevent @TraceID, 176, 50, @on
exec sp_trace_setevent @TraceID, 176, 3, @on
exec sp_trace_setevent @TraceID, 176, 11, @on
exec sp_trace_setevent @TraceID, 176, 35, @on
exec sp_trace_setevent @TraceID, 176, 51, @on
exec sp_trace_setevent @TraceID, 176, 4, @on
exec sp_trace_setevent @TraceID, 176, 12, @on
exec sp_trace_setevent @TraceID, 176, 28, @on
exec sp_trace_setevent @TraceID, 176, 60, @on
exec sp_trace_setevent @TraceID, 176, 21, @on
exec sp_trace_setevent @TraceID, 176, 29, @on
exec sp_trace_setevent @TraceID, 176, 37, @on
exec sp_trace_setevent @TraceID, 176, 45, @on
exec sp_trace_setevent @TraceID, 176, 6, @on
exec sp_trace_setevent @TraceID, 176, 14, @on
exec sp_trace_setevent @TraceID, 176, 46, @on
exec sp_trace_setevent @TraceID, 134, 7, @on
exec sp_trace_setevent @TraceID, 134, 23, @on
exec sp_trace_setevent @TraceID, 134, 39, @on
exec sp_trace_setevent @TraceID, 134, 8, @on
exec sp_trace_setevent @TraceID, 134, 40, @on
exec sp_trace_setevent @TraceID, 134, 64, @on
exec sp_trace_setevent @TraceID, 134, 1, @on
exec sp_trace_setevent @TraceID, 134, 41, @on
exec sp_trace_setevent @TraceID, 134, 49, @on
exec sp_trace_setevent @TraceID, 134, 6, @on
exec sp_trace_setevent @TraceID, 134, 10, @on
exec sp_trace_setevent @TraceID, 134, 14, @on
exec sp_trace_setevent @TraceID, 134, 26, @on
exec sp_trace_setevent @TraceID, 134, 34, @on
exec sp_trace_setevent @TraceID, 134, 42, @on
exec sp_trace_setevent @TraceID, 134, 50, @on
exec sp_trace_setevent @TraceID, 134, 3, @on
exec sp_trace_setevent @TraceID, 134, 11, @on
exec sp_trace_setevent @TraceID, 134, 35, @on
exec sp_trace_setevent @TraceID, 134, 43, @on
exec sp_trace_setevent @TraceID, 134, 51, @on
exec sp_trace_setevent @TraceID, 134, 4, @on
exec sp_trace_setevent @TraceID, 134, 12, @on
exec sp_trace_setevent @TraceID, 134, 28, @on
exec sp_trace_setevent @TraceID, 134, 60, @on
exec sp_trace_setevent @TraceID, 134, 29, @on
exec sp_trace_setevent @TraceID, 134, 37, @on
exec sp_trace_setevent @TraceID, 170, 7, @on
exec sp_trace_setevent @TraceID, 170, 23, @on
exec sp_trace_setevent @TraceID, 170, 8, @on
exec sp_trace_setevent @TraceID, 170, 40, @on
exec sp_trace_setevent @TraceID, 170, 64, @on
exec sp_trace_setevent @TraceID, 170, 1, @on
exec sp_trace_setevent @TraceID, 170, 9, @on
exec sp_trace_setevent @TraceID, 170, 41, @on
exec sp_trace_setevent @TraceID, 170, 49, @on
exec sp_trace_setevent @TraceID, 170, 10, @on
exec sp_trace_setevent @TraceID, 170, 26, @on
exec sp_trace_setevent @TraceID, 170, 34, @on
exec sp_trace_setevent @TraceID, 170, 42, @on
exec sp_trace_setevent @TraceID, 170, 50, @on
exec sp_trace_setevent @TraceID, 170, 3, @on
exec sp_trace_setevent @TraceID, 170, 11, @on
exec sp_trace_setevent @TraceID, 170, 19, @on
exec sp_trace_setevent @TraceID, 170, 35, @on
exec sp_trace_setevent @TraceID, 170, 43, @on
exec sp_trace_setevent @TraceID, 170, 51, @on
exec sp_trace_setevent @TraceID, 170, 4, @on
exec sp_trace_setevent @TraceID, 170, 12, @on
exec sp_trace_setevent @TraceID, 170, 28, @on
exec sp_trace_setevent @TraceID, 170, 60, @on
exec sp_trace_setevent @TraceID, 170, 21, @on
exec sp_trace_setevent @TraceID, 170, 29, @on
exec sp_trace_setevent @TraceID, 170, 37, @on
exec sp_trace_setevent @TraceID, 170, 6, @on
exec sp_trace_setevent @TraceID, 170, 14, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 1, 0, 7, N'select'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Приложение SQL Server Profiler - 342699b4-dba3-411d-9901-e3ba875c146a'
exec sp_trace_setfilter @TraceID, 34, 0, 6, N'sp_configure'
exec sp_trace_setfilter @TraceID, 34, 1, 6, N'sp_dbcmptlevel'
exec sp_trace_setfilter @TraceID, 34, 1, 6, N'sp_update_job'
exec sp_trace_setfilter @TraceID, 34, 1, 6, N'sp_add_job'
exec sp_trace_setfilter @TraceID, 34, 1, 6, N'sp_delete_job'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

Аудит mssql 2005 с помощью SQL Server Profiler и создания задания в агенте.

После этого берем код и вставляем в мой шаблон

-- ======================================================================================================

-- Author: Константин Москвичёв

-- Create date: 13/04/2015

-- Description:    0.2b

-- Описание: Запускает трасировку для аудита. 

-- ======================================================================================================

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @filename nvarchar(128)
declare @filecount int

--указываем максимальный размер файла
set @maxfilesize = 1000

--указываем путь и имя файла, к имени файла дописывается текушия дата и время

set @filename = '\\192.168.0.38\Backup\audit_rx6600\TracE'+CONVERT(CHAR(20),GETDATE(),112)+REPLACE(CONVERT(CHAR(20),GETDATE(),108),':','')

-- НАстройка параметров трасировки
exec	@rc = sp_trace_create
	@traceid = @TraceID output, 
	@options = 2, --опция 2 https://msdn.microsoft.com/ru-ru/library/ms190362.aspx
	@tracefile = @filename, --имя трасировки
	@maxfilesize = @maxfilesize, -- максимальный размер трасировки
	@stoptime = NULL, -- не ограниченое время выполнения
	@filecount =  10 -- не больше 50 файлов хранить
	
if (@rc != 0) goto error


-- Сюда можно добавить любые параметры трасировки из sql Profile. 
-- В данный момент задать применен шаблон audit с фильтром 
-- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 109, 7, @on
exec sp_trace_setevent @TraceID, 109, 23, @on
exec sp_trace_setevent @TraceID, 109, 39, @on
exec sp_trace_setevent @TraceID, 109, 8, @on
exec sp_trace_setevent @TraceID, 109, 40, @on
exec sp_trace_setevent @TraceID, 109, 64, @on
exec sp_trace_setevent @TraceID, 109, 9, @on
exec sp_trace_setevent @TraceID, 109, 41, @on
exec sp_trace_setevent @TraceID, 109, 49, @on
exec sp_trace_setevent @TraceID, 109, 10, @on
exec sp_trace_setevent @TraceID, 109, 26, @on
exec sp_trace_setevent @TraceID, 109, 42, @on
exec sp_trace_setevent @TraceID, 109, 50, @on
exec sp_trace_setevent @TraceID, 109, 3, @on
exec sp_trace_setevent @TraceID, 109, 11, @on
exec sp_trace_setevent @TraceID, 109, 35, @on
exec sp_trace_setevent @TraceID, 109, 43, @on
exec sp_trace_setevent @TraceID, 109, 51, @on
exec sp_trace_setevent @TraceID, 109, 4, @on
exec sp_trace_setevent @TraceID, 109, 12, @on
exec sp_trace_setevent @TraceID, 109, 28, @on
exec sp_trace_setevent @TraceID, 109, 44, @on
exec sp_trace_setevent @TraceID, 109, 60, @on
exec sp_trace_setevent @TraceID, 109, 21, @on
exec sp_trace_setevent @TraceID, 109, 29, @on
exec sp_trace_setevent @TraceID, 109, 37, @on
exec sp_trace_setevent @TraceID, 109, 6, @on
exec sp_trace_setevent @TraceID, 109, 14, @on
exec sp_trace_setevent @TraceID, 109, 38, @on
exec sp_trace_setevent @TraceID, 111, 7, @on
exec sp_trace_setevent @TraceID, 111, 23, @on
exec sp_trace_setevent @TraceID, 111, 8, @on
exec sp_trace_setevent @TraceID, 111, 40, @on
exec sp_trace_setevent @TraceID, 111, 64, @on
exec sp_trace_setevent @TraceID, 111, 9, @on
exec sp_trace_setevent @TraceID, 111, 41, @on
exec sp_trace_setevent @TraceID, 111, 49, @on
exec sp_trace_setevent @TraceID, 111, 6, @on
exec sp_trace_setevent @TraceID, 111, 10, @on
exec sp_trace_setevent @TraceID, 111, 14, @on
exec sp_trace_setevent @TraceID, 111, 26, @on
exec sp_trace_setevent @TraceID, 111, 38, @on
exec sp_trace_setevent @TraceID, 111, 50, @on
exec sp_trace_setevent @TraceID, 111, 3, @on
exec sp_trace_setevent @TraceID, 111, 11, @on
exec sp_trace_setevent @TraceID, 111, 35, @on
exec sp_trace_setevent @TraceID, 111, 51, @on
exec sp_trace_setevent @TraceID, 111, 4, @on
exec sp_trace_setevent @TraceID, 111, 12, @on
exec sp_trace_setevent @TraceID, 111, 28, @on
exec sp_trace_setevent @TraceID, 111, 60, @on
exec sp_trace_setevent @TraceID, 111, 21, @on
exec sp_trace_setevent @TraceID, 111, 29, @on
exec sp_trace_setevent @TraceID, 104, 7, @on
exec sp_trace_setevent @TraceID, 104, 23, @on
exec sp_trace_setevent @TraceID, 104, 8, @on
exec sp_trace_setevent @TraceID, 104, 40, @on
exec sp_trace_setevent @TraceID, 104, 64, @on
exec sp_trace_setevent @TraceID, 104, 9, @on
exec sp_trace_setevent @TraceID, 104, 41, @on
exec sp_trace_setevent @TraceID, 104, 49, @on
exec sp_trace_setevent @TraceID, 104, 6, @on
exec sp_trace_setevent @TraceID, 104, 10, @on
exec sp_trace_setevent @TraceID, 104, 14, @on
exec sp_trace_setevent @TraceID, 104, 26, @on
exec sp_trace_setevent @TraceID, 104, 42, @on
exec sp_trace_setevent @TraceID, 104, 50, @on
exec sp_trace_setevent @TraceID, 104, 3, @on
exec sp_trace_setevent @TraceID, 104, 11, @on
exec sp_trace_setevent @TraceID, 104, 35, @on
exec sp_trace_setevent @TraceID, 104, 43, @on
exec sp_trace_setevent @TraceID, 104, 51, @on
exec sp_trace_setevent @TraceID, 104, 4, @on
exec sp_trace_setevent @TraceID, 104, 12, @on
exec sp_trace_setevent @TraceID, 104, 28, @on
exec sp_trace_setevent @TraceID, 104, 60, @on
exec sp_trace_setevent @TraceID, 104, 5, @on
exec sp_trace_setevent @TraceID, 104, 21, @on
exec sp_trace_setevent @TraceID, 128, 7, @on
exec sp_trace_setevent @TraceID, 128, 23, @on
exec sp_trace_setevent @TraceID, 128, 8, @on
exec sp_trace_setevent @TraceID, 128, 40, @on
exec sp_trace_setevent @TraceID, 128, 64, @on
exec sp_trace_setevent @TraceID, 128, 1, @on
exec sp_trace_setevent @TraceID, 128, 41, @on
exec sp_trace_setevent @TraceID, 128, 49, @on
exec sp_trace_setevent @TraceID, 128, 6, @on
exec sp_trace_setevent @TraceID, 128, 10, @on
exec sp_trace_setevent @TraceID, 128, 14, @on
exec sp_trace_setevent @TraceID, 128, 26, @on
exec sp_trace_setevent @TraceID, 128, 34, @on
exec sp_trace_setevent @TraceID, 128, 50, @on
exec sp_trace_setevent @TraceID, 128, 3, @on
exec sp_trace_setevent @TraceID, 128, 11, @on
exec sp_trace_setevent @TraceID, 128, 35, @on
exec sp_trace_setevent @TraceID, 128, 51, @on
exec sp_trace_setevent @TraceID, 128, 4, @on
exec sp_trace_setevent @TraceID, 128, 12, @on
exec sp_trace_setevent @TraceID, 128, 28, @on
exec sp_trace_setevent @TraceID, 128, 60, @on
exec sp_trace_setevent @TraceID, 128, 21, @on
exec sp_trace_setevent @TraceID, 128, 29, @on
exec sp_trace_setevent @TraceID, 128, 37, @on
exec sp_trace_setevent @TraceID, 130, 7, @on
exec sp_trace_setevent @TraceID, 130, 23, @on
exec sp_trace_setevent @TraceID, 130, 39, @on
exec sp_trace_setevent @TraceID, 130, 8, @on
exec sp_trace_setevent @TraceID, 130, 40, @on
exec sp_trace_setevent @TraceID, 130, 64, @on
exec sp_trace_setevent @TraceID, 130, 1, @on
exec sp_trace_setevent @TraceID, 130, 41, @on
exec sp_trace_setevent @TraceID, 130, 49, @on
exec sp_trace_setevent @TraceID, 130, 6, @on
exec sp_trace_setevent @TraceID, 130, 10, @on
exec sp_trace_setevent @TraceID, 130, 14, @on
exec sp_trace_setevent @TraceID, 130, 26, @on
exec sp_trace_setevent @TraceID, 130, 34, @on
exec sp_trace_setevent @TraceID, 130, 42, @on
exec sp_trace_setevent @TraceID, 130, 50, @on
exec sp_trace_setevent @TraceID, 130, 3, @on
exec sp_trace_setevent @TraceID, 130, 11, @on
exec sp_trace_setevent @TraceID, 130, 35, @on
exec sp_trace_setevent @TraceID, 130, 43, @on
exec sp_trace_setevent @TraceID, 130, 51, @on
exec sp_trace_setevent @TraceID, 130, 4, @on
exec sp_trace_setevent @TraceID, 130, 12, @on
exec sp_trace_setevent @TraceID, 130, 28, @on
exec sp_trace_setevent @TraceID, 130, 60, @on
exec sp_trace_setevent @TraceID, 130, 21, @on
exec sp_trace_setevent @TraceID, 130, 29, @on
exec sp_trace_setevent @TraceID, 130, 37, @on
exec sp_trace_setevent @TraceID, 107, 7, @on
exec sp_trace_setevent @TraceID, 107, 23, @on
exec sp_trace_setevent @TraceID, 107, 8, @on
exec sp_trace_setevent @TraceID, 107, 40, @on
exec sp_trace_setevent @TraceID, 107, 64, @on
exec sp_trace_setevent @TraceID, 107, 1, @on
exec sp_trace_setevent @TraceID, 107, 9, @on
exec sp_trace_setevent @TraceID, 107, 41, @on
exec sp_trace_setevent @TraceID, 107, 49, @on
exec sp_trace_setevent @TraceID, 107, 10, @on
exec sp_trace_setevent @TraceID, 107, 26, @on
exec sp_trace_setevent @TraceID, 107, 34, @on
exec sp_trace_setevent @TraceID, 107, 42, @on
exec sp_trace_setevent @TraceID, 107, 50, @on
exec sp_trace_setevent @TraceID, 107, 3, @on
exec sp_trace_setevent @TraceID, 107, 11, @on
exec sp_trace_setevent @TraceID, 107, 35, @on
exec sp_trace_setevent @TraceID, 107, 43, @on
exec sp_trace_setevent @TraceID, 107, 51, @on
exec sp_trace_setevent @TraceID, 107, 4, @on
exec sp_trace_setevent @TraceID, 107, 12, @on
exec sp_trace_setevent @TraceID, 107, 28, @on
exec sp_trace_setevent @TraceID, 107, 60, @on
exec sp_trace_setevent @TraceID, 107, 21, @on
exec sp_trace_setevent @TraceID, 107, 29, @on
exec sp_trace_setevent @TraceID, 107, 37, @on
exec sp_trace_setevent @TraceID, 107, 6, @on
exec sp_trace_setevent @TraceID, 107, 14, @on
exec sp_trace_setevent @TraceID, 118, 7, @on
exec sp_trace_setevent @TraceID, 118, 23, @on
exec sp_trace_setevent @TraceID, 118, 8, @on
exec sp_trace_setevent @TraceID, 118, 40, @on
exec sp_trace_setevent @TraceID, 118, 64, @on
exec sp_trace_setevent @TraceID, 118, 1, @on
exec sp_trace_setevent @TraceID, 118, 9, @on
exec sp_trace_setevent @TraceID, 118, 41, @on
exec sp_trace_setevent @TraceID, 118, 49, @on
exec sp_trace_setevent @TraceID, 118, 6, @on
exec sp_trace_setevent @TraceID, 118, 10, @on
exec sp_trace_setevent @TraceID, 118, 14, @on
exec sp_trace_setevent @TraceID, 118, 26, @on
exec sp_trace_setevent @TraceID, 118, 34, @on
exec sp_trace_setevent @TraceID, 118, 50, @on
exec sp_trace_setevent @TraceID, 118, 3, @on
exec sp_trace_setevent @TraceID, 118, 11, @on
exec sp_trace_setevent @TraceID, 118, 35, @on
exec sp_trace_setevent @TraceID, 118, 51, @on
exec sp_trace_setevent @TraceID, 118, 4, @on
exec sp_trace_setevent @TraceID, 118, 12, @on
exec sp_trace_setevent @TraceID, 118, 28, @on
exec sp_trace_setevent @TraceID, 118, 60, @on
exec sp_trace_setevent @TraceID, 118, 5, @on
exec sp_trace_setevent @TraceID, 118, 21, @on
exec sp_trace_setevent @TraceID, 118, 29, @on
exec sp_trace_setevent @TraceID, 118, 37, @on
exec sp_trace_setevent @TraceID, 114, 7, @on
exec sp_trace_setevent @TraceID, 114, 23, @on
exec sp_trace_setevent @TraceID, 114, 8, @on
exec sp_trace_setevent @TraceID, 114, 40, @on
exec sp_trace_setevent @TraceID, 114, 64, @on
exec sp_trace_setevent @TraceID, 114, 1, @on
exec sp_trace_setevent @TraceID, 114, 9, @on
exec sp_trace_setevent @TraceID, 114, 41, @on
exec sp_trace_setevent @TraceID, 114, 49, @on
exec sp_trace_setevent @TraceID, 114, 2, @on
exec sp_trace_setevent @TraceID, 114, 10, @on
exec sp_trace_setevent @TraceID, 114, 26, @on
exec sp_trace_setevent @TraceID, 114, 34, @on
exec sp_trace_setevent @TraceID, 114, 50, @on
exec sp_trace_setevent @TraceID, 114, 3, @on
exec sp_trace_setevent @TraceID, 114, 11, @on
exec sp_trace_setevent @TraceID, 114, 19, @on
exec sp_trace_setevent @TraceID, 114, 35, @on
exec sp_trace_setevent @TraceID, 114, 51, @on
exec sp_trace_setevent @TraceID, 114, 59, @on
exec sp_trace_setevent @TraceID, 114, 4, @on
exec sp_trace_setevent @TraceID, 114, 12, @on
exec sp_trace_setevent @TraceID, 114, 28, @on
exec sp_trace_setevent @TraceID, 114, 44, @on
exec sp_trace_setevent @TraceID, 114, 60, @on
exec sp_trace_setevent @TraceID, 114, 5, @on
exec sp_trace_setevent @TraceID, 114, 21, @on
exec sp_trace_setevent @TraceID, 114, 29, @on
exec sp_trace_setevent @TraceID, 114, 37, @on
exec sp_trace_setevent @TraceID, 114, 6, @on
exec sp_trace_setevent @TraceID, 114, 14, @on
exec sp_trace_setevent @TraceID, 176, 7, @on
exec sp_trace_setevent @TraceID, 176, 23, @on
exec sp_trace_setevent @TraceID, 176, 8, @on
exec sp_trace_setevent @TraceID, 176, 40, @on
exec sp_trace_setevent @TraceID, 176, 64, @on
exec sp_trace_setevent @TraceID, 176, 1, @on
exec sp_trace_setevent @TraceID, 176, 9, @on
exec sp_trace_setevent @TraceID, 176, 41, @on
exec sp_trace_setevent @TraceID, 176, 49, @on
exec sp_trace_setevent @TraceID, 176, 10, @on
exec sp_trace_setevent @TraceID, 176, 26, @on
exec sp_trace_setevent @TraceID, 176, 34, @on
exec sp_trace_setevent @TraceID, 176, 50, @on
exec sp_trace_setevent @TraceID, 176, 3, @on
exec sp_trace_setevent @TraceID, 176, 11, @on
exec sp_trace_setevent @TraceID, 176, 35, @on
exec sp_trace_setevent @TraceID, 176, 51, @on
exec sp_trace_setevent @TraceID, 176, 4, @on
exec sp_trace_setevent @TraceID, 176, 12, @on
exec sp_trace_setevent @TraceID, 176, 28, @on
exec sp_trace_setevent @TraceID, 176, 60, @on
exec sp_trace_setevent @TraceID, 176, 21, @on
exec sp_trace_setevent @TraceID, 176, 29, @on
exec sp_trace_setevent @TraceID, 176, 37, @on
exec sp_trace_setevent @TraceID, 176, 45, @on
exec sp_trace_setevent @TraceID, 176, 6, @on
exec sp_trace_setevent @TraceID, 176, 14, @on
exec sp_trace_setevent @TraceID, 176, 46, @on
exec sp_trace_setevent @TraceID, 134, 7, @on
exec sp_trace_setevent @TraceID, 134, 23, @on
exec sp_trace_setevent @TraceID, 134, 39, @on
exec sp_trace_setevent @TraceID, 134, 8, @on
exec sp_trace_setevent @TraceID, 134, 40, @on
exec sp_trace_setevent @TraceID, 134, 64, @on
exec sp_trace_setevent @TraceID, 134, 1, @on
exec sp_trace_setevent @TraceID, 134, 41, @on
exec sp_trace_setevent @TraceID, 134, 49, @on
exec sp_trace_setevent @TraceID, 134, 6, @on
exec sp_trace_setevent @TraceID, 134, 10, @on
exec sp_trace_setevent @TraceID, 134, 14, @on
exec sp_trace_setevent @TraceID, 134, 26, @on
exec sp_trace_setevent @TraceID, 134, 34, @on
exec sp_trace_setevent @TraceID, 134, 42, @on
exec sp_trace_setevent @TraceID, 134, 50, @on
exec sp_trace_setevent @TraceID, 134, 3, @on
exec sp_trace_setevent @TraceID, 134, 11, @on
exec sp_trace_setevent @TraceID, 134, 35, @on
exec sp_trace_setevent @TraceID, 134, 43, @on
exec sp_trace_setevent @TraceID, 134, 51, @on
exec sp_trace_setevent @TraceID, 134, 4, @on
exec sp_trace_setevent @TraceID, 134, 12, @on
exec sp_trace_setevent @TraceID, 134, 28, @on
exec sp_trace_setevent @TraceID, 134, 60, @on
exec sp_trace_setevent @TraceID, 134, 29, @on
exec sp_trace_setevent @TraceID, 134, 37, @on
exec sp_trace_setevent @TraceID, 170, 7, @on
exec sp_trace_setevent @TraceID, 170, 23, @on
exec sp_trace_setevent @TraceID, 170, 8, @on
exec sp_trace_setevent @TraceID, 170, 40, @on
exec sp_trace_setevent @TraceID, 170, 64, @on
exec sp_trace_setevent @TraceID, 170, 1, @on
exec sp_trace_setevent @TraceID, 170, 9, @on
exec sp_trace_setevent @TraceID, 170, 41, @on
exec sp_trace_setevent @TraceID, 170, 49, @on
exec sp_trace_setevent @TraceID, 170, 10, @on
exec sp_trace_setevent @TraceID, 170, 26, @on
exec sp_trace_setevent @TraceID, 170, 34, @on
exec sp_trace_setevent @TraceID, 170, 42, @on
exec sp_trace_setevent @TraceID, 170, 50, @on
exec sp_trace_setevent @TraceID, 170, 3, @on
exec sp_trace_setevent @TraceID, 170, 11, @on
exec sp_trace_setevent @TraceID, 170, 19, @on
exec sp_trace_setevent @TraceID, 170, 35, @on
exec sp_trace_setevent @TraceID, 170, 43, @on
exec sp_trace_setevent @TraceID, 170, 51, @on
exec sp_trace_setevent @TraceID, 170, 4, @on
exec sp_trace_setevent @TraceID, 170, 12, @on
exec sp_trace_setevent @TraceID, 170, 28, @on
exec sp_trace_setevent @TraceID, 170, 60, @on
exec sp_trace_setevent @TraceID, 170, 21, @on
exec sp_trace_setevent @TraceID, 170, 29, @on
exec sp_trace_setevent @TraceID, 170, 37, @on
exec sp_trace_setevent @TraceID, 170, 6, @on
exec sp_trace_setevent @TraceID, 170, 14, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Приложение SQL Server Profiler - 342699b4-dba3-411d-9901-e3ba875c146a'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Spaceman Stock Planner'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Spaceman Professional'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Spaceman Viewer'
exec sp_trace_setfilter @TraceID, 34, 0, 6, N'sp_configure'
exec sp_trace_setfilter @TraceID, 34, 1, 6, N'sp_dbcmptlevel'
exec sp_trace_setfilter @TraceID, 34, 1, 6, N'sp_update_job'
exec sp_trace_setfilter @TraceID, 34, 1, 6, N'sp_add_job'
exec sp_trace_setfilter @TraceID, 34, 1, 6, N'sp_delete_job'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

После этого создаем задание в MSSQL

Аудит mssql 2005 с помощью SQL Server Profiler и создания задания в агенте.

Я делаю в задание первым шагом удаление трасировок:

declare @trace_num int

set @trace_num =  (Select traceid from ::fn_trace_getinfo(default)
where property = 2 and convert (varchar (500),value) like '%tracE'+convert(char(8),getdate(),112)+'%')

select @trace_num

--ставит на паузу потом стопает
EXEC sp_trace_setstatus @traceid =@trace_num  , @status = 0
EXEC sp_trace_setstatus @traceid =@trace_num  , @status = 2

Второй шаг добавление трасировки :

Аудит mssql 2005 с помощью SQL Server Profiler и создания задания в агенте.

Третьй и четвертый шаг отправка письма с удачным завершением или нет.

Чтобы посомтреть файл трасировки можно открыть в самом SQL Server Profiler или использовать код:

SELECT *
FROM fn_trace_gettable(N'\\192.168.0.38\Backup\audit_rx6600\TracE20160125            094104.trc',DEFAULT)

Старт задания по запуску sql

Аудит mssql 2005 с помощью SQL Server Profiler и создания задания в агенте.

Фалы: адуит через профайл

Посмотреть задание по id

SELECT *
	from msdb.dbo.sysjobs 
where job_id = N'5a082f0b-d52b-4f5c-a4b8-dcb6b5aeb6c0';

 

Similar Posts:

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *