.::: Create SQL Audit in SQL Server MSSQL for example Schema Change & Permission Change :::.

 A. Schema Audit
1. Schema change Audit script

USE [master]
GO

/****** Object:  Audit [Schema_Change_Sample]    Script Date: 8/3/2022 6:08:55 PM ******/
CREATE SERVER AUDIT [Schema_Change_Sample]
TO FILE
(    FILEPATH = N'C:\audit\' ---- Change Audit file path
    ,MAXSIZE = 20 MB
    ,MAX_ROLLOVER_FILES = 5
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'e5abe6c5-f451-41f3-936c-b1da1c0786ce'
)
ALTER SERVER AUDIT [Schema_Change_Sample] WITH (STATE = ON)
GO
 

2. Schema change Audit specification.

 
USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [Schema_Change_Specification]
FOR SERVER AUDIT [Schema_Change_Sample]
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO
 

B. User Login Audit

1. Login permission audit script

USE [master]
GO

/****** Object:  Audit [Login_Permission_Change_Sample]    Script Date: 8/3/2022 6:10:31 PM ******/
CREATE SERVER AUDIT [Login_Permission_Change_Sample]
TO FILE
(    FILEPATH = N'C:\DB\MSSQL_Server_Audit\' ---Change Audit file
    ,MAXSIZE = 15 MB
    ,MAX_ROLLOVER_FILES = 3
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'ad6f9c87-5b56-44d4-b18f-d835aa4cf9b8'
)
ALTER SERVER AUDIT [Login_Permission_Change_Sample] WITH (STATE = ON)
GO
 

2. Login permission audit specification

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [Login_Permission_Change_Specification]
FOR SERVER AUDIT [Login_Permission_Change_Sample]
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP)
WITH (STATE = ON)
GO
 

C. Sample Log SCHEMA
 

D. Sample Log User

 E. Sample Full