What are the audit policies required to generate events for an SQL Server report?

What are the audit policies required to generate events for an SQL Server report?

Existing Reports vs SQL Server Policies  

S. no.

Report Group

Total Reports

Report Name

Criteria

Required Server-level Audit Action Types

1

SQL Server Events

2

All Events

-

-

Important Events

-

-

2

SQLServer Trend Report

2

Read Event Trend

(ACTIONID:SL) AND (CLASSTYPE:U)

SCHEMA_OBJECT_ACCESS_GROUP

Write Event Trend

((ACTIONID:IN) OR (ACTIONID:UP) OR (ACTIONID:DL)) AND (CLASSTYPE:U)

SCHEMA_OBJECT_ACCESS_GROUP

3

DDL Auditing Report

22

Created Databases

(ACTIONID:CR) AND (CLASSTYPE:DB)

DATABASE_CHANGE_GROUP

Dropped Databases

(ACTIONID:DR) AND (CLASSTYPE:DB)

DATABASE_CHANGE_GROUP

Altered Databases

(ACTIONID:AL) AND (CLASSTYPE:DB)

DATABASE_CHANGE_GROUP

Created Tables

(ACTIONID:CR) AND (CLASSTYPE:U)

SCHEMA_OBJECT_CHANGE_GROUP

Dropped Tables

(ACTIONID:DR) AND (CLASSTYPE:U)

SCHEMA_OBJECT_CHANGE_GROUP

Altered Tables

(ACTIONID:AL) AND (CLASSTYPE:U)

SCHEMA_OBJECT_CHANGE_GROUP

Created Views

(ACTIONID:CR) AND (CLASSTYPE:V)

SCHEMA_OBJECT_CHANGE_GROUP

Dropped Views

(ACTIONID:DR) AND (CLASSTYPE:V)

SCHEMA_OBJECT_CHANGE_GROUP

Altered Views

(ACTIONID:AL) AND (CLASSTYPE:V)

SCHEMA_OBJECT_CHANGE_GROUP

Created Stored Procedures

(ACTIONID:CR) AND (CLASSTYPE:P)

SCHEMA_OBJECT_CHANGE_GROUP

Dropped Stored Procedures

(ACTIONID:DR) AND (CLASSTYPE:P)

SCHEMA_OBJECT_CHANGE_GROUP

Altered Stored Procedures

(ACTIONID:AL) AND (CLASSTYPE:P)

SCHEMA_OBJECT_CHANGE_GROUP

Created Index

(ACTIONID:CR) AND (CLASSTYPE:IX)

SCHEMA_OBJECT_CHANGE_GROUP

Dropped Index

(ACTIONID:DR) AND (CLASSTYPE:IX)

SCHEMA_OBJECT_CHANGE_GROUP

Altered Index

(ACTIONID:AL) AND (CLASSTYPE:IX)

SCHEMA_OBJECT_CHANGE_GROUP

Created Triggers

(ACTIONID:CR) AND (CLASSTYPE:TR)

SCHEMA_OBJECT_CHANGE_GROUP

Dropped Triggers

(ACTIONID:DR) AND (CLASSTYPE:TR)

SCHEMA_OBJECT_CHANGE_GROUP

Altered Triggers

(ACTIONID:AL) AND (CLASSTYPE:TR)

SCHEMA_OBJECT_CHANGE_GROUP

Created Schemas

(ACTIONID:CR) AND (CLASSTYPE:SC)

DATABASE_OBJECT_CHANGE_GROUP

Altered Schemas

(ACTIONID:AL) AND (CLASSTYPE:SC)

DATABASE_OBJECT_CHANGE_GROUP

Dropped Schemas

(ACTIONID:DR) AND (CLASSTYPE:SC)

DATABASE_OBJECT_CHANGE_GROUP

DDL Events Summary

-

 

4

DML Auditing Report

12

Selected Tables

(ACTIONID:SL) AND (CLASSTYPE:U)

SCHEMA_OBJECT_ACCESS_GROUP

Inserted Tables

(ACTIONID:IN) AND (CLASSTYPE:U)

SCHEMA_OBJECT_ACCESS_GROUP

Updated Tables

(ACTIONID:UP) AND (CLASSTYPE:U)

SCHEMA_OBJECT_ACCESS_GROUP

Deleted Tables

(ACTIONID:DL) AND (CLASSTYPE:U)

SCHEMA_OBJECT_ACCESS_GROUP

Execute Command

(ACTIONID:EX)

SCHEMA_OBJECT_ACCESS_GROUP

Receive Command

(ACTIONID:RC)

SCHEMA_OBJECT_ACCESS_GROUP

Check reference command executed

(ACTIONID:RF)

  1. DATABASE_OBJECT_ACCESS_GROUP

  2. SCHEMA_OBJECT_ACCESS_GROUP

Inserted Schemas

(ACTIONID:IN) AND (CLASSTYPE:SC)

-

Selected Schemas

(ACTIONID:SL) AND (CLASSTYPE:SC)

-

Updated Schemas

(ACTIONID:UP) AND (CLASSTYPE:SC)

-

Deleted Schemas

(ACTIONID:DL) AND (CLASSTYPE:SC)

-

DML Events Summary

-

-

5

Auditing Account Management

27

User Created

((ACTIONID:CR) AND ((CLASSTYPE:US) OR (CLASSTYPE:SU)))

DATABASE_PRINCIPAL_CHANGE_GROUP

User Dropped

((ACTIONID:DR) AND ((CLASSTYPE:US) OR (CLASSTYPE:SU)))

DATABASE_PRINCIPAL_CHANGE_GROUP

User Altered

((ACTIONID:AL) AND ((CLASSTYPE:US) OR (CLASSTYPE:SU)))

DATABASE_PRINCIPAL_CHANGE_GROUP

Login Created

((ACTIONID:CR) AND ((CLASSTYPE:LX) OR (CLASSTYPE:SL) OR (CLASSTYPE:WL) OR (CLASSTYPE:WG)))

SERVER_PRINCIPAL_CHANGE_GROUP

Login Dropped

((ACTIONID:DR) AND ((CLASSTYPE:LX) OR (CLASSTYPE:SL) OR (CLASSTYPE:WL) OR (CLASSTYPE:WG)))

SERVER_PRINCIPAL_CHANGE_GROUP

Login Altered

((ACTIONID:LGNM) AND ((CLASSTYPE:LX) OR (CLASSTYPE:SL) OR (CLASSTYPE:WL) OR (CLASSTYPE:WG)))

SERVER_PRINCIPAL_CHANGE_GROUP

DataBase Role Created

(ACTIONID:CR) AND (CLASSTYPE:RL)

DATABASE_PRINCIPAL_CHANGE_GROUP

DataBase Role Dropped

(ACTIONID:DR) AND (CLASSTYPE:RL)

DATABASE_PRINCIPAL_CHANGE_GROUP

DataBase Role Altered

(ACTIONID:AL) AND (CLASSTYPE:RL)

DATABASE_PRINCIPAL_CHANGE_GROUP

Application Role Created

(ACTIONID:CR) AND (CLASSTYPE:AR)

DATABASE_PRINCIPAL_CHANGE_GROUP

Application Role Dropped

(ACTIONID:DR) AND (CLASSTYPE:AR)

DATABASE_PRINCIPAL_CHANGE_GROUP

Application Role Altered

(ACTIONID:AL) AND (CLASSTYPE:AR)

DATABASE_PRINCIPAL_CHANGE_GROUP

Credential Created

(ACTIONID:CR) AND (CLASSTYPE:CD)

SERVER_OBJECT_CHANGE_GROUP

Credential Dropped

(ACTIONID:DR) AND (CLASSTYPE:CD)

SERVER_OBJECT_CHANGE_GROUP

Credential Altered

(ACTIONID:AL) AND (CLASSTYPE:CD)

SERVER_OBJECT_CHANGE_GROUP

All Password Changes

((ACTIONID:PWC) OR (ACTIONID:PWCS) OR (ACTIONID:PWR) OR (ACTIONID:PWRS)) AND (SEVERITY:3)

  1. APPLICATION_ROLE_CHANGE_PASSWORD_GROUP

  2. LOGIN_CHANGE_PASSWORD_GROUP

  3. USER_CHANGE_PASSWORD_GROUP

Own Password Changes

(ACTIONID:PWCS) AND (SEVERITY:3)

  1. USER_CHANGE_PASSWORD_GROUP

  2. LOGIN_CHANGE_PASSWORD_GROUP

Failed Own password changes

(ACTIONID:PWCS) AND (SEVERITY:4)

  1. USER_CHANGE_PASSWORD_GROUP

  2. LOGIN_CHANGE_PASSWORD_GROUP

Password Changes

(ACTIONID:PWC) AND (SEVERITY:3)

  1. USER_CHANGE_PASSWORD_GROUP

  2. APPLICATION_ROLE_CHANGE_PASSWORD_GROUP

  3. LOGIN_CHANGE_PASSWORD_GROUP

Password Changes Failed

(ACTIONID:PWC) AND (SEVERITY:4)

  1. USER_CHANGE_PASSWORD_GROUP

  2. APPLICATION_ROLE_CHANGE_PASSWORD_GROUP

  3. LOGIN_CHANGE_PASSWORD_GROUP

Password Reset

(ACTIONID:PWR) AND (SEVERITY:3)

  1. LOGIN_CHANGE_PASSWORD_GROUP

  2. USER_CHANGE_PASSWORD_GROUP

Password Reset Failed

(ACTIONID:PWR) AND (SEVERITY:4)

  1. LOGIN_CHANGE_PASSWORD_GROUP

  2. USER_CHANGE_PASSWORD_GROUP

Own password resets

(ACTIONID:PWRS) AND (SEVERITY:3)

  1. LOGIN_CHANGE_PASSWORD_GROUP

  2. USER_CHANGE_PASSWORD_GROUP

Failed Own password resets

(ACTIONID:PWRS) AND (SEVERITY:4)

  1. LOGIN_CHANGE_PASSWORD_GROUP

  2. USER_CHANGE_PASSWORD_GROUP

Unlocked accounts

(ACTIONID:PWU)

LOGIN_CHANGE_PASSWORD_GROUP

Enabled users

(ACTIONID:LGEA)

SERVER_PRINCIPAL_CHANGE_GROUP

Disabled Users

(ACTIONID:LGDA)

SERVER_PRINCIPAL_CHANGE_GROUP

6

Auditing Server Report

38

Database Backup Report

(ACTIONID:BA)

  1. BACKUP_RESTORE_GROUP

  2. DATABASE_OBJECT_CHANGE_GROUP

  3. SERVER_OBJECT_CHANGE_GROUP

Database Restore

(ACTIONID:RS)

BACKUP_RESTORE_GROUP

Database Backup Failed

(ACTIONID:BA) AND (SEVERITY:4)

  1. BACKUP_RESTORE_GROUP

  2. DATABASE_OBJECT_CHANGE_GROUP

  3. SERVER_OBJECT_CHANGE_GROUP

Transaction Log Backup Report

(ACTIONID:BAL)

BACKUP_RESTORE_GROUP

Admin Authority Changes Report

(ACTIONID:APRL)

  1. SERVER_ROLE_MEMBER_CHANGE_GROUP

  2. DATABASE_ROLE_MEMBER_CHANGE_GROUP

Permission Changes Report

((ACTIONID:G) OR (ACTIONID:GWG) OR (ACTIONID:R) OR (ACTIONID:RWG) OR (ACTIONID:RWC))

  1. DATABASE_OBJECT_PERMISSION_CHANGE_GROUP

  2. DATABASE_PERMISSION_CHANGE_GROUP

  3. SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP

  4. SERVER_OBJECT_PERMISSION_CHANGE_GROUP

  5. SERVER_PERMISSION_CHANGE_GROUP

Owner Changes Report

(ACTIONID:TO)

  1. DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP

  2. DATABASE_OWNERSHIP_CHANGE_GROUP

  3. SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP

  4. SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP

Created server roles

(ACTIONID:CR) AND (CLASSTYPE:SG)

SERVER_PRINCIPAL_CHANGE_GROUP

Dropped server roles

(ACTIONID:DR) AND (CLASSTYPE:SG)

SERVER_PRINCIPAL_CHANGE_GROUP

Altered server roles

(ACTIONID:AL) AND (CLASSTYPE:SG)

SERVER_PRINCIPAL_CHANGE_GROUP

Created Server Audits

(ACTIONID:CR) AND (CLASSTYPE:A)

AUDIT_CHANGE_GROUP

Dropped Server Audits

(ACTIONID:DR) AND (CLASSTYPE:A)

AUDIT_CHANGE_GROUP

Altered server audits

(ACTIONID:AL) AND (CLASSTYPE:A)

AUDIT_CHANGE_GROUP

Created Server Audit Specifications

(ACTIONID:CR) AND (CLASSTYPE:SA)

AUDIT_CHANGE_GROUP

Dropped Server Audit Specifications

(ACTIONID:DR) AND (CLASSTYPE:SA)

AUDIT_CHANGE_GROUP

Altered Server Audit Specifications

(ACTIONID:AL) AND (CLASSTYPE:SA)

AUDIT_CHANGE_GROUP

Created Database Audit Specifications

(ACTIONID:CR) AND (CLASSTYPE:DA)

AUDIT_CHANGE_GROUP

Dropped Database Audit Specifications

(ACTIONID:DR) AND (CLASSTYPE:DA)

AUDIT_CHANGE_GROUP

Altered Database Audit Specifications

(ACTIONID:AL) AND (CLASSTYPE:DA)

AUDIT_CHANGE_GROUP

Changed Audit Sessions

(ACTIONID:AUSC)

-

Shutdown and Failure Audits

(ACTIONID:AUSF)

AUDIT_CHANGE_GROUP

Trace Audit C2 On

(ACTIONID:C2ON)

TRACE_CHANGE_GROUP

Trace Audit C2 Off

(ACTIONID:C2OF)

TRACE_CHANGE_GROUP

Started Trace Audits

(ACTIONID:TASA)

TRACE_CHANGE_GROUP

Stopped Trace Audits

(ACTIONID:TASP)

TRACE_CHANGE_GROUP

Server Startups

(ACTIONID:SVSR) AND (CLASSTYPE:SR)

SERVER_STATE_CHANGE_GROUP

Server shutdowns

(ACTIONID:SVSD) AND (CLASSTYPE:SR)

SERVER_STATE_CHANGE_GROUP

Status Reports

((ACTIONID:SVSR) OR (ACTIONID:SVSD)) AND (CLASSTYPE:SR)

SERVER_STATE_CHANGE_GROUP

Logons

((ACTIONID:LGIS) OR (RULENAME:18453) OR (RULENAME:18454) OR (RULENAME:18455) OR (RULENAME:28046))

SUCCESSFUL_LOGIN_GROUP

Failed Logons

((ACTIONID:LGIF) OR (RULENAME:18401) OR (RULENAME:18451) OR (RULENAME:18461) OR (RULENAME:18462) OR (RULENAME:18463) OR (RULENAME:18464) OR (RULENAME:18465) OR (RULENAME:18466) OR (RULENAME:18467) OR (RULENAME:18468) OR (RULENAME:18470) OR (RULENAME:18471) OR (RULENAME:18487) OR (RULENAME:18488) OR (RULENAME:15537) OR (RULENAME:15538) OR (RULENAME:18456) OR (RULENAME:18486) OR (RULENAME:28048))

FAILED_LOGIN_GROUP

Logout Accounts

(ACTIONID:LGO)

LOGOUT_GROUP

Top logons based on users

(ACTIONID:LGIS)

SUCCESSFUL_LOGIN_GROUP

Top logons based on remote devices

(ACTIONID:LGIS)

SUCCESSFUL_LOGIN_GROUP

Top failure logons based on users

(ACTIONID:LGIF)

FAILED_LOGIN_GROUP

Top Failure Logons based on Remote Devices

(ACTIONID:LGIF)

FAILED_LOGIN_GROUP

Logons Trend

(ACTIONID:LGIS)

SUCCESSFUL_LOGIN_GROUP

Failed Logons Trend

(ACTIONID:LGIF)

FAILED_LOGIN_GROUP

Event Trend report

-

-

7

Attack Reports

6

Privilege Abuse

(((ACTIONID:BA) OR (ACTIONID:CR) OR (ACTIONID:DR) OR (ACTIONID:AL) OR (ACTIONID:SL) OR (ACTIONID:UP) OR (ACTIONID:DL)) AND (SEVERITY:4))

  1. AUDIT_CHANGE_GROUP

  2. BACKUP_RESTORE_GROUP

  3. DATABASE_CHANGE_GROUP

  4. DATABASE_OBJECT_CHANGE_GROUP

  5. DATABASE_PRINCIPAL_CHANGE_GROUP

  6. SCHEMA_OBJECT_ACCESS_GROUP

  7. SCHEMA_OBJECT_CHANGE_GROUP

  8. SERVER_OBJECT_CHANGE_GROUP

  9. SERVER_PRINCIPAL_CHANGE_GROUP

Unauthorized Copies of Sensitive Data

(((ACTIONID:BA) OR (ACTIONID:SL)) AND (SEVERITY:4))

  1. BACKUP_RESTORE_GROUP

  2. DATABASE_OBJECT_CHANGE_GROUP

  3. SCHEMA_OBJECT_ACCESS_GROUP

  4. SERVER_OBJECT_CHANGE_GROUP

Account Lockouts

(EVENTID:18486) AND (SOURCE:MSSQLSERVER)

-

Storage Media Exposure

(ACTIONID:BA)

  1. BACKUP_RESTORE_GROUP

  2. DATABASE_OBJECT_CHANGE_GROUP

  3. SERVER_OBJECT_CHANGE_GROUP

SQL Injection

(MESSAGE:\"80040e14\")

-

Denial of Service

((EVENTID:17809) OR (EVENTID:17810) OR (EVENTID:17889) OR (EVENTID:18458))

-

8

DBCC Information Report

3

Check Database Events

((RULENAME:610) OR (RULENAME:8440) OR (RULENAME:9100) OR (RULENAME:15612) OR (RULENAME:15615))

-

Check Catalog Events

((RULENAME:211) OR (RULENAME:427))

-

DBCC Error Events

((RULENAME:2509) OR (RULENAME:2510) OR (RULENAME:17557) OR (RULENAME:2514))

-

9

Permission Denied Report

4

Database Permission Denied

((RULENAME:262) OR (RULENAME:916))

-

Column Permission Denied

(RULENAME:230)

-

Object Permission Denied

((RULENAME:229) OR (RULENAME:300))

-

Alter Database Permission

(RULENAME:5011)

-

10

Integrity Report

2

Integrity Audit

(RULENAME:806)

-

Failure Following Successful Events

(RULENAME:825)

-

11

Host Activity Report

1

Processes Killed by Host

(RULENAME:18100)

-

12

Violation Report

1

Access Violation

((RULENAME:17308) OR (RULENAME:17311))

-

13

Additional Security Reports

1

Additional Security Events

-

 

14

Server Log Reports

4

Successful Trusted Logins

(logintype:Trusted)

-

Successful Non-Trusted Logins

(logintype:Non-Trusted)

-

Failed User Logins

(fail_login:1)

-

Insufficient Resources Events

((errorno:701) OR (errorno:17803)) AND (severity:17)

-

No. of Advanced Auditing Reports

17

Total Reports

142


                New to ADManager Plus?

                  New to ADSelfService Plus?

                    • Related Articles

                    • Why are SQL Server audit logs not collected?

                      Case 1: Is Advanced Auditing enabled? Open EventLog Analyzer and go to Settings > Database Audit > SQL Servers. The DDL/DML Monitoring column should show Manage for the required instance. If it says Not configured, then edit the required instance, ...
                    • Why are some SQL Server reports showing no data?

                      Case 1: Are the required audit policies configured? Open SQL Server Management Studio application in the Windows machine in which SQL Server is installed, and connect to the required instance. Click the Security option. The Server Audit ...
                    • How do I fix the issue of being unable to configure the SQL Server application?

                      To open the SQL Server Configuration Manager to view the configurations of an SQL Server instance: In the machine where SQL Server is running, connect to the Microsoft Management Console via Run > mmc. In the Microsoft Management Console, go to File ...
                    • How to collect the users Enabled/Disabled SQL logs?

                      The Enable/Disable logs will be recorded in the Event Viewer in the following scenarios: In SQL Server Management Studio, Security ⇾ Logins ⇾ Right-click on any user ⇾ Properties ⇾ Status ⇾ Login section ⇾ select Disabled/Enabled. By executing the ...
                    • How to restore a backed up instance on a different server?

                      1. Copy the entire <EventLog Analyzer Home> directory to the new server. It is highly recommended that the new location be the same as the older one. Eg: If the product was installed on an old server in C:\ManageEngine\EventLog, please make sure you ...