USE [master]
GO
----创建审核,命名规范AuditServer_描述
CREATE SERVER AUDIT [AuditServer_All]
TO FILE 
(    FILEPATH = N''D:\Audit\AuditServer_All''  ----文件路径
    ,MAXSIZE = 2 GB                     ----文件最大大小，单位可以是MB、GB、TB
    ,MAX_FILES = 2147483647             ----最大文件数，最大2147483647也就是无限制
-----,MAX_ROLLOVER_FILES = 2147483647   ----最大滚动更新文件数，最大2147483647也就是无限制，不能和MAX_FILES一起配置
    ,RESERVE_DISK_SPACE = ON            ----保留磁盘空间
)
WITH
(    QUEUE_DELAY = 1000                  ----队列延时，默认1S
    ,ON_FAILURE = CONTINUE              ----审核失败继续，还可以指定SHUTDOWN关闭数据库服务器，但是必须有相关权限
)

GO

---启用
USE [master]
GO
ALTER SERVER AUDIT AuditServer_All WITH(STATE=ON);
---禁用
USE [master]
GO
ALTER SERVER AUDIT AuditServer_All WITH(STATE=OFF);

----创建服务器审核规范,命名规范:AuditSpecification_描述（和审核描述保持一致）
USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION AuditSpecification_All
FOR SERVER AUDIT AuditServer_All
    ADD (FAILED_LOGIN_GROUP),
    ADD (SERVER_OBJECT_CHANGE_GROUP),
    ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
    ADD (AUDIT_CHANGE_GROUP)
GO

--启用
GO
ALTER SERVER AUDIT SPECIFICATION AuditSpecification_All WITH(STATE=ON);
GO
---禁用,注意作业会影响禁用
ALTER SERVER AUDIT SPECIFICATION AuditSpecification_All WITH(STATE=OFF);

----创建登入名,并授予sysadmin角色
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N''123456'', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [test]
GO

USE [master]
GO
----创建审核,审核只能创建在服务器级别，命名规范AuditDatabase_数据库名_描述
CREATE SERVER AUDIT [AuditDatabase_chenmh_AllObjectChange]
TO FILE 
(    FILEPATH = N''D:\Audit\AuditDatabase_chenmh_AllObjectChange''    ----文件路径
    ,MAXSIZE = 2 GB                     ----文件最大大小，单位可以是MB、GB、TB
    ,MAX_FILES = 2147483647             ----最大文件数，最大2147483647也就是无限制
-----,MAX_ROLLOVER_FILES = 2147483647   ----最大滚动更新文件数，最大2147483647也就是无限制，不能和MAX_FILES一起配置
    ,RESERVE_DISK_SPACE = ON            ----保留磁盘空间
)
WITH
(    QUEUE_DELAY = 1000                  ----队列延时，默认1S
    ,ON_FAILURE = CONTINUE              ----审核失败继续，还可以指定SHUTDOWN关闭数据库服务器，但是必须有相关权限
)

GO
---启用
USE [master]
GO
ALTER SERVER AUDIT AuditDatabase_chenmh_AllObjectChange WITH(STATE=ON);
---禁用
USE [master]
GO
--ALTER SERVER AUDIT AuditDatabase_chenmh_AllObjectChange WITH(STATE=OFF);

CREATE DATABASE Audit;
GO

----创建数据库审核规范,命名规范：AuditSpecification_描述（和审核描述保持一致）
USE [Audit]
GO
CREATE DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange
FOR SERVER AUDIT AuditDatabase_Audit_AllObjectChange
    ---DDL相关操作
    ADD (DATABASE_CHANGE_GROUP),
    ADD (DATABASE_OBJECT_CHANGE_GROUP),
    ADD (SCHEMA_OBJECT_CHANGE_GROUP),
    ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
    ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
    ---DML相关操作,PUBLIC代表所有用户
    -----ADD (SELECT ON SCHEMA::[dbo] BY PUBLIC),
    ADD (DELETE ON SCHEMA::[dbo] BY PUBLIC),
    ADD (INSERT ON SCHEMA::[dbo] BY PUBLIC),
    ADD (UPDATE ON SCHEMA::[dbo] BY PUBLIC);
GO

--启用
USE [Audit]
GO
ALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange WITH(STATE=ON);
---禁用,注意作业会影响禁用
USE [Audit]
GO
--ALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange WITH(STATE=OFF);

USE [Audit]
GO
CREATE TABLE a(id int not null);
GO
ALTER TABLE a ADD name varchar(10);
GO
INSERT INTO a values(1,''a'')
GO
DELETE FROM a WHERE id=1;
GO
DROP TABLE a;

----查询审核日志
select * from  sys.fn_get_audit_file(''D:\Audit\AuditServerFile_66D8F97A-B495-4CB4-83EA-564D1ECF9988_0_131604628132230000.sqlaudit'',DEFAULT, DEFAULT) AS a


--服务器审核相关视图
SELECT * FROM SYS.server_audits
SELECT * FROM sys.server_audit_specifications
SELECT * FROM sys.server_audit_specification_details

SELECT sa.name as AuditName,
sa.type_desc AS StoreType,
sas.name AS AuditSpecificationsName,
sasd.audit_action_id,
sasd.audit_action_name,
sasd.class_desc as AuditGrade
FROM SYS.server_audits sa INNER JOIN sys.server_audit_specifications sas ON sa.audit_guid=sas.audit_guid
INNER JOIN  sys.server_audit_specification_details sasd ON sas.server_specification_id=sasd.server_specification_id
LEFT JOIN sys.dm_audit_actions daa ON sasd.audit_action_id=daa.action_id AND sasd.class_desc=daa.class_desc


--数据库审核规范视图
SELECT * FROM chenmh.sys.database_audit_specifications
SELECT * FROM chenmh.sys.database_audit_specification_details

SELECT sa.name as AuditName,
sa.type_desc AS StoreType,
sas.name AS AuditSpecificationsName,
sasd.audit_action_id,
sasd.audit_action_name,
sasd.class_desc as AuditGrade
FROM SYS.server_audits sa INNER JOIN chenmh.sys.database_audit_specifications sas ON sa.audit_guid=sas.audit_guid
INNER JOIN  chenmh.sys.database_audit_specification_details sasd ON sas.database_specification_id=sasd.database_specification_id
LEFT JOIN sys.dm_audit_actions daa ON sasd.audit_action_id=daa.action_id AND sasd.class_desc=daa.class_desc

----
SELECT * FROM  sys.dm_audit_actions  WHERE action_id=''DL''

--删除服务器审核规范，先禁用才能删除
USE [master]
GO
ALTER SERVER  AUDIT SPECIFICATION AuditSpecification_All WITH (STATE=OFF)
GO
DROP SERVER AUDIT SPECIFICATION AuditSpecification_All
GO

--删除服务器审核对象，先禁用才能删除
USE [master]
ALTER SERVER AUDIT AuditServer_All WITH (STATE=OFF)
GO
DROP SERVER AUDIT AuditServer_All;

USE [master]
GO
----创建审核,审核只能创建在服务器级别，命名规范AuditDatabase_数据库名_描述
CREATE SERVER AUDIT [AuditDatabase_Audit_AllObjectChange]
TO FILE 
(    FILEPATH = N''C:\Audit\AuditDatabase_Audit_AllObjectChange''    ----文件路径
    ,MAXSIZE = 2 GB                     ----文件最大大小，单位可以是MB、GB、TB
    ,MAX_FILES = 2147483647             ----最大文件数，最大2147483647也就是无限制
-----,MAX_ROLLOVER_FILES = 2147483647   ----最大滚动更新文件数，最大2147483647也就是无限制，不能和MAX_FILES一起配置
    ,RESERVE_DISK_SPACE = ON            ----保留磁盘空间
)
WITH
(    QUEUE_DELAY = 1000                  ----队列延时，默认1S
    ,ON_FAILURE = CONTINUE              ----审核失败继续，还可以指定SHUTDOWN关闭数据库服务器，但是必须有相关权限
)

GO
---启用
USE [master]
GO
ALTER SERVER AUDIT AuditDatabase_Audit_AllObjectChange WITH(STATE=ON);
---禁用
USE [master]
GO
--ALTER SERVER AUDIT AuditDatabase_Audit_AllObjectChange WITH(STATE=OFF);

CREATE DATABASE Audit;
GO
----创建数据库审核规范,命名规范：AuditSpecification_描述（和审核描述保持一致）
USE [audit]
GO
CREATE DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange
FOR SERVER AUDIT AuditDatabase_Audit_AllObjectChange
    ---DDL相关操作
    ADD (DATABASE_CHANGE_GROUP),
    ADD (DATABASE_OBJECT_CHANGE_GROUP),
    ADD (SCHEMA_OBJECT_CHANGE_GROUP),
    ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
    ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
    ---DML相关操作,PUBLIC代表所有用户
    -----ADD (SELECT ON SCHEMA::[dbo] BY PUBLIC),
    ADD (DELETE ON SCHEMA::[dbo] BY PUBLIC),
    ADD (INSERT ON SCHEMA::[dbo] BY PUBLIC),
    ADD (UPDATE ON SCHEMA::[dbo] BY PUBLIC);
GO

--启用
USE [audit]
GO
ALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange WITH(STATE=ON);

-------========================创建服务器审核=======================
USE [master]
GO
----创建审核,命名规范AuditServer_描述
CREATE SERVER AUDIT [AuditServer_All]
TO FILE 
(    FILEPATH = N''C:\Audit\AuditServer_All\''  ----文件路径
    ,MAXSIZE = 2 GB                     ----文件最大大小，单位可以是MB、GB、TB
    ,MAX_FILES = 2147483647             ----最大文件数，最大2147483647也就是无限制
-----,MAX_ROLLOVER_FILES = 2147483647   ----最大滚动更新文件数，最大2147483647也就是无限制，不能和MAX_FILES一起配置
    ,RESERVE_DISK_SPACE = ON            ----保留磁盘空间
)
WITH
(    QUEUE_DELAY = 1000                  ----队列延时，默认1S
    ,ON_FAILURE = CONTINUE              ----审核失败继续，还可以指定SHUTDOWN关闭数据库服务器，但是必须有相关权限
)

GO

---启用服务器审核
USE [master]
GO
ALTER SERVER AUDIT AuditServer_All WITH(STATE=ON);

---------================创建服务器审核规范====================
USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION AuditSpecification_All
FOR SERVER AUDIT AuditServer_All
    ADD (FAILED_LOGIN_GROUP),
    ADD (SERVER_OBJECT_CHANGE_GROUP),
    ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
    ADD (AUDIT_CHANGE_GROUP)
GO

--启用
GO
ALTER SERVER AUDIT SPECIFICATION AuditSpecification_All WITH(STATE=ON);
GO

------------============================创建数据库基本审核规范=====================================================
USE [master]
GO
----创建审核,审核只能创建在服务器级别，命名规范AuditDatabase_数据库名_描述
CREATE SERVER AUDIT [AuditDatabase_Audit_AllObjectChange]
TO FILE 
(    FILEPATH = N''C:\Audit\AuditDatabase_Audit_AllObjectChange''    ----文件路径
    ,MAXSIZE = 2 GB                     ----文件最大大小，单位可以是MB、GB、TB
    ,MAX_FILES = 2147483647             ----最大文件数，最大2147483647也就是无限制
-----,MAX_ROLLOVER_FILES = 2147483647   ----最大滚动更新文件数，最大2147483647也就是无限制，不能和MAX_FILES一起配置
    ,RESERVE_DISK_SPACE = ON            ----保留磁盘空间
)
WITH
(    QUEUE_DELAY = 1000                  ----队列延时，默认1S
    ,ON_FAILURE = CONTINUE              ----审核失败继续，还可以指定SHUTDOWN关闭数据库服务器，但是必须有相关权限
)

GO
---启用
USE [master]
GO
ALTER SERVER AUDIT AuditDatabase_Audit_AllObjectChange WITH(STATE=ON);


-----===============关联数据库审核规范与服务器审核====================
USE [audit]
GO
----禁用数据库审核规范
ALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange WITH(STATE=OFF);
GO
---将数据库审核规范关联服务器审核
ALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange
FOR SERVER AUDIT AuditDatabase_Audit_AllObjectChange
WITH (STATE =ON)
GO
----启用数据库审核规范
ALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange WITH(STATE=ON);