Создание аудита в SQL Server 2005/2008

--Создание аудита в SQL Server 2005
USE [master]
GO

/****** Поменяйте пути !!!!  ******/
/****** --------------- ******/
CREATE DATABASE [AuditDB] ON  PRIMARY
( NAME = N'AuditDB_Data', FILENAME = N'E:\SQLData\AuditDB_Data.MDF' , SIZE = 3000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON
( NAME = N'AuditDB_Log', FILENAME = N'E:\SQLData\AuditDB_Log.LDF' , SIZE = 3000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO

USE [AuditDB]
GO

/****** Object:  Table [dbo].[_AuditTable]    Script Date: 01/28/2010 18:02:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[_AuditTable](
        [login_time] [DATETIME] NOT NULL,
        [nt_username] [VARCHAR](100) NOT NULL,
        [database_name] [VARCHAR](100) NOT NULL,
        [program_name] [VARCHAR](100) NULL,
        [hostname] [VARCHAR](100) NULL,
        [curr_time] [DATETIME] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO



USE [msdb]
GO
/****** Object:  Job [AuditJob]    Script Date: 01/28/2010 20:46:11 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 01/28/2010 20:46:11 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @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'AuditJob',
                @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 [Insert]    Script Date: 01/28/2010 20:46:12 ******/
EXEC @ReturnCode = msdb.dbo.SP_ADD_JOBSTEP @job_id=@jobId, @step_name=N'Insert',
                @step_id=1,
                @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'TSQL',
                @command=N'set nocount ON  
if (object_id(''tempdb..#who'') is not null)
   drop table #who
create table #who (spid int, status varchar(200),login varchar(200), hostname varchar(20),BlkBy varchar(50), dbname varchar(200), command varchar(1000), cpuTime int, diskio int, lastbatch varchar(200), programname varchar(500),spid2 int, ri int)

insert into #who
exec sp_who2


insert into [AuditDB].dbo.[_AuditTable] ([login_time],[nt_username],[database_name],[program_name],[hostname])
select distinct GETDATE(), [login], dbname, programname, hostname  from #who
where spid > 50
set nocount OFF',
                @database_name=N'master',
                @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.SP_UPDATE_JOB @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.SP_ADD_JOBSCHEDULE @job_id=@jobId, @name=N'Every5min',
                @enabled=1,
                @freq_type=4,
                @freq_interval=1,
                @freq_subday_type=4,
                @freq_subday_interval=1,
                @freq_relative_interval=0,
                @freq_recurrence_factor=0,
                @active_start_date=20100101,
                @active_end_date=99991231,
                @active_start_time=0,
                @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.SP_ADD_JOBSERVER @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

--Создание аудита в SQL Server 2008
USE [master]
GO

/****** Object:  Database [AuditDB]    Script Date: 01/28/2010 18:38:26 ******/
CREATE DATABASE [AuditDB] ON  PRIMARY
( NAME = N'AuditDB_Data', FILENAME = N'E:\SQLData\AuditDB_Data.MDF' , SIZE = 3000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON
( NAME = N'AuditDB_Log', FILENAME = N'E:\SQLData\AuditDB_Log.LDF' , SIZE = 3000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO

USE [AuditDB]
GO

/****** Object:  Table [dbo].[_AuditTable]    Script Date: 01/28/2010 18:02:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[_AuditTable](
        [login_time] [DATETIME] NOT NULL,
        [nt_username] [VARCHAR](100) NOT NULL,
        [database_name] [VARCHAR](100) NOT NULL,
        [program_name] [VARCHAR](100) NULL,
        [hostname] [VARCHAR](100) NULL,
        [curr_time] [DATETIME] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO



USE [msdb]
GO

/****** Object:  Job [AuditJob]    Script Date: 01/28/2010 19:36:14 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 01/28/2010 19:36:14 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @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'AuditJob',
                @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 [Insert]    Script Date: 01/28/2010 19:36:15 ******/
EXEC @ReturnCode = msdb.dbo.SP_ADD_JOBSTEP @job_id=@jobId, @step_name=N'Insert',
                @step_id=1,
                @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'TSQL',
                @command=N'set nocount ON  
if (object_id(''tempdb..#who'') is not null)
   drop table #who
create table #who (spid int, status varchar(200),login varchar(200), hostname varchar(20),BlkBy varchar(50), dbname varchar(200), command varchar(1000), cpuTime int, diskio int, lastbatch varchar(200), programname varchar(500),spid2 int, ri int)

insert into #who
exec sp_who2


insert into [AuditDB].dbo.[_AuditTable] ([login_time],[nt_username],[database_name],[program_name],[hostname])
select distinct GETDATE(), [login], dbname, programname, hostname  from #who
where spid > 50
set nocount OFF',
                @database_name=N'master',
                @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.SP_UPDATE_JOB @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.SP_ADD_JOBSCHEDULE @job_id=@jobId, @name=N'Every5min',
                @enabled=1,
                @freq_type=4,
                @freq_interval=1,
                @freq_subday_type=4,
                @freq_subday_interval=1,
                @freq_relative_interval=0,
                @freq_recurrence_factor=0,
                @active_start_date=20100101,
                @active_end_date=99991231,
                @active_start_time=0,
                @active_end_time=235959,
                @schedule_uid=N'3de1dbd4-df30-49ee-8686-6ddb57764f73'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.SP_ADD_JOBSERVER @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO