Audited events in ApexSQL Audit

Summary
This article provides a list of SQL Server events for each operation type group used in ApexSQL Audit GUI (DDL, DML, Query, Execute, Error, Warning, and Security).

Description
The events audited by ApexSQL Audit are divided into 7 groups, by operation type.

DDL – Data Definition language operations

  • A database object altered – for example, using ALTER TABLE, ALTER VIEW or ALTER INDEX statements
  • A database object created – for example, using CREATE TABLE, CREATE VIEW or CREATE INDEX statements
  • A database object deleted – for example, using DROP TABLE or DROP INDEX statements
  • Schema changes, such as object renaming, using the sp_rename SQL stored procedure
  • DDL trigger manipulations, such as Enable/Disable trigger
  • Revert
  • Update statistics

DML – Data manipulation language operations

  • Data inserts – for example, using the INSERT INTO statement
  • Data deletes – for example, using the DELETE FROM statement
  • Data updates – for example, using the UPDATE statement
  • A data file import into a SQL table or view, using the BULK INSERT statement
  • Reading text, ntext, or image columns, using the READTEXT statement
  • Text, ntext, and image column updates, using the WRITETEXT statement
  • Text, ntext, or image column updates, using the UPDATETEXT statement
  • Merging the records in a target and source SQL tables using the MERGE statement
  • Removing all rows from table – TRUNCATE TABLE statement

Query

  • Execution of the SELECT statement
  • Execution of the SELECT INTO statement
  • An XQuery expression (for querying and processing XML data) is executed

Execute

  • A remote procedure call is completed
  • Execution of a SQL stored procedure is completed

Error

  • An event that requires attention has occurred. For example cancel, client-interrupt requests, or broken client connections
  • A background job is terminated abnormally
  • A task is blocked. System tasks or tasks waiting on resources not affected by deadlocks are not included
  • A message is logged into the SQL Server error log
  • An event is logged into the Microsoft Windows event log
  • An exception has occurred on a SQL Server instance
  • A page is suspect and added to the suspect pages table in msdb

Warning

  • Warnings related to bitmap filter usage in SQL queries. For example, when bitmap filters are disabled in a SQL query
  • A SQL query exceeds the CPU threshold specified
  • Communication buffers in a parallel query plan are temporarily written to the tempdb database
  • Memory grant warnings during the execution of a SQL statement or stored procedure
  • A hash recursion or hash bailout has occurred
  • Column statistics are not available for the optimizer
  • A SQL query without a JOIN is executed
  • Sort operations within a SQL query do not fit into memory

Security

  • The GRANT, REVOKE or DENY statement is executed on a SQL Server instance
  • The GRANT, REVOKE, or DENY statement is executed on a server – level object
  • The GRANT, REVOKE, or DENY statement is executed on a SQL database
  • The GRANT, REVOKE, or DENY statement is executed on a SQL database object
  • A SQL login property is modified. The following can be used to invoke this event: sp_defaultdb and sp_defaultlanguage stored procedures and the ALTER LOGIN statement
  • A SQL user is successfully logged to a SQL Server instance
  • An unsuccessful SQL user login to a SQL Server instance
  • Microsoft Windows login permissions are added or removed. The following SQL stored procedures can be used to invoke this event: sp_grantlogin,sp_revokelogin and sp_denylogin
  • A database user has logged off a SQL Server instance
  • A SQL login is added or removed as a SQL database user. The following SQL stored procedures can be used to invoke this event: sp_grantdbaccess, sp_revokedbaccess, sp_adduser, and sp_dropuser
  • A SQL login is added or removed from a fixed SQL Server role. The following SQL stored procedures can be used to invoke this event: sp_addsrvrolemember and sp_dropsrvrolemember
  • A SQL login is added to or removed from a SQL database role. The following SQL stored procedures can be used to invoke this event: sp_addrolemember, sp_changegroup, and sp_droprolemember
  • A SQL database role is added to or removed from a SQL database. The following SQL stored procedures can be used to invoke this event: sp_addrole and sp_droprole
  • A SQL Server login is added or removed. The following SQL stored procedures can be used to invoke this event: sp_addlogin and sp_droplogin
  • A password is changed for a SQL application role
  • A password is changed for a SQL user or an impersonation within a SQL database has occurred. For example, using the EXECUTE AS or SETUSER statements
  • A SQL principal (for example a user) is created, altered, or dropped
  • A backup or restore command is executed
  • SQL Service Broker dialog security messages are generated
  • SQL Service Broker transport security messages are generated
  • SQL database mirroring transport security messages are generated
  • A SQL database operation, such as checkpoint or subscribe query notification has occurred
  • The DBCC command is executed
  • A SQL Server instance connects to and communicates with a full-text filter daemon process
  • A SQL audit trace is modified. For example, using the ALTER TRACE statement, creating and configuring a SQL trace, setting a filter on a SQL trace
  • A SQL database is created, altered, or dropped
  • A SQL database object is accessed
  • A server – level object is created, altered, or dropped
  • The CREATE, ALTER, or DROP statement is executed on a SQL database object
  • An owner of a server-level object is changed
  • An owner of a SQL Server database is changed using the ALTER AUTHORIZATION statement
  • An owner of a database object is changed
  • Database object (table, procedure, or function) permission is changed. The ALTER AUTHORIZATION statement can be used to invoke this event
  • A database object is accessed (for example using the SELECT statement)
  • The CREATE, ALTER or DROP statement is executed on a server-level object
  • The CREATE, ALTER or DROP statement is executed for a database object
  • A security audit operation is executed. For example altering settings, resources, external access, or authorization
  • An impersonation within a SQL Server instance has occurred. For example using the EXECUTE AS statement
  • A SQL Server service state is changed
  • SQL statement permission is used (for CREATE TABLE, CREATE FUNCTION, CREATE VIEW, etc.)