sqlserver 审核

-- 创建测试库
use master
go
create database prod
go
use prod
go
create table table1(ID int primary key ,name varchar(20),weight numeric(10,2));
insert into table1 values(1,'union',70.2),(3,'mobil',75.1)
go




--创建审核 

USE [master]

GO

CREATE SERVER AUDIT [Audit-prod]
TO FILE
( FILEPATH = N'F:\sqlserver_audit\audit'
 ,MAXSIZE = 50 MB
,MAX_ROLLOVER_FILES = 2147483647
 ,RESERVE_DISK_SPACE = ON
)

WITH
( QUEUE_DELAY = 1000
 ,ON_FAILURE = CONTINUE
 --AUDIT_GUID=uniqueidentifier 
)
--WHERE object_name='table1'

GO

--Enable Server Audit

alter server audit [Audit-prod] with (state=on)






--创建审核数据库规范

USE prod

GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-prod]

FOR SERVER AUDIT [Audit-prod]

ADD (SCHEMA_OBJECT_CHANGE_GROUP),

ADD (SELECT,DELETE,INSERT,UPDATE ON OBJECT::[dbo].[table1] BY [public])

WITH (STATE=ON)

GO


-- 做一些操作
use prod
go
select * from dbo.table1
where ID=1;
update dbo.table1
set name='china1'
where ID=3;
alter table dbo.table1 alter column name varchar(30);
alter table dbo.table1 add newCol varchar(20);
go



-- 查看审计结果
SELECT event_time
    , action_id
    , succeeded
    , session_id
    , session_server_principal_name
    , object_name
    , statement
    , file_name
    , audit_file_offset
from sys.fn_get_audit_file('F:\sqlserver_audit\audit\*',default,default)