Appendix C - Audit Policies and Security Event IDs

It is recommended to use the 'Add Extended Event' button on 'Audit Policy Configuration' to capture the extended events. The available Extended Events depends on the version of the SQL Server installed on the machine. Snare captured the available Extended Events and categorized them in Channel and Categories. These channels and categories also depend on the version of SQL Server installed on the machine. There is also a section 'Group Level Extended Events' on 'Add Extended Event' form. This section contains the most common Extended Events that are available on all the versions of the SQL Server. The following table shows the Extended Events in each of the group.

Extended Event Package Name	Extended Event Name	Extended Event Description
Logon or Logoff
sqlserver	login	Occurs when a successful connection is made to the Server. This event is fired for new connection or when connections are reused from a connection pool
sqlserver	logout	Occurs when a user has logged out of logged off the Server. This event is required in traces that will be used for Replay
sqlserver	prelogin_traceid	Occurs when a prelogin containing a traceid arrives on a connection.
sqlserver	ual_instrument_called	Occurs when a successful connection to the Server is reported to Windows using the User Access Logging API. This event is only fired for new connections. Connection resets are not tracked.
sqlserver	connectivity_ring_buffer_recorded	Occurs when there is a serverinitiated connection closure such as login failed.
sqlserver	security_authentication_perf_login	Authentication performance statistics login function
sqlserver	security_authentication_perf_find_login	Authentication performance statistics find login function
sqlserver	security_authentication_perf_create_logintoken	Authentication performance statistics create login token
Account Administration
sqlserver	object_altered	Occurs when an object was altered by the ALTER statement. This event is raised two times for every ALTER operation. The event is raised when the operation begins and when the operation is either rolled back or committed. Add the nt_username or server_principal_name actions to this event to determine who altered the object.
sqlserver	object_created	Occurs when an object was created by the CREATE statement. This event is raised two times for every CREATE operation. The event is raised when the operation begins and when the operation is either rolled back or committed. Add the nt_username or server_principal_name actions to this event to determine who altered the object.
sqlserver	object_deleted	Occurs when an object was deleted by the DROP statement. This event is raised two times for every DROP operation. The event is raised when the operation begins and when the operation is either rolled back or committed. Add the nt_username or server_principal_name actions to this event to determine who altered the object.
sqlserver	hadr_db_manager_user_control	Change user status for a hadron controlled database.
Group : Database Object Access
sqlserver	broker_transmission_object_delete_begin	Broker transmission object worktable save end
sqlserver	broker_transmission_object_delete_end	Broker transmission object worktable save end
Query Tracking
sqlserver	sql_transaction	Occurs when a SQL Server transaction begins completes rolls back or executes a savepoint. Use this event to monitor transaction behavior when troubleshooting applications triggers or stored procedures.
sqlserver	sql_batch_starting	Occurs when a TransactSQL batch has started executing.
sqlserver	sql_statement_starting	Occurs when a TransactSQL statement has started.
sqlserver	degree_of_parallelism	Occurs every time that SQL Server executes a SELECT INSERT UPDATE or DELETE statement. Use this event to monitor if a query is running in parallel.
sqlserver	plan_guide_successful	Occurs when a plan is created for a query or batch that includes a plan guide. Use this event to determine when plan guides are successfully applied.
sqlserver	plan_guide_unsuccessful	Occurs when an execution plan is created for a query or batch without using the provided plan guide. Use this event to identify queries and batches with invalid plan guides. Use the sys.fn_validate_plan_guide function to detect errors in the plan guide.
sqlserver	query_cache_removal_statistics	Occurs when a query plan is removed from the plan cache and the historical statistics for the object are about to be destroyed.
sqlserver	query_no_cqscan_cache_due_to_memory_limitation	No CQSCAN cache due to memory limitation.
Data Definition
sqlos	node_created	Node created
sqlserver	object_created	Occurs when an object was created by the CREATE statement. This event is raised two times for every CREATE operation. The event is raised when the operation begins and when the operation is either rolled back or committed. Add the nt_username or server_principal_name actions to this event to determine who altered the object.
sqlserver	object_deleted	Occurs when an object was deleted by the DROP statement. This event is raised two times for every DROP operation. The event is raised when the operation begins and when the operation is either rolled back or committed. Add the nt_username or server_principal_name actions to this event to determine who altered the object.
sqlserver	object_altered	Occurs when an object was altered by the ALTER statement. This event is raised two times for every ALTER operation. The event is raised when the operation begins and when the operation is either rolled back or committed. Add the nt_username or server_principal_name actions to this event to determine who altered the object.
sqlserver	database_created	Database created
sqlserver	alwayson_ddl_executed	Occurs when Always On DDL is executed including CREATE ALTER or DROP
sqlserver	alter_table_update_data	Occurs when an ALTER TABLE ADD column operation results in an UPDATE that affects every row. This event signals a potentially expensive DDL statement.
sqlserver	hadr_db_manager_user_control	Change user status for a hadron controlled database.
sqlserver	recovery_simple_log_truncate	Queued writes of dirty pages to allow truncation of simplerecovery database. This event is only applicable to databases where indirect checkpoint is enabled.
sqlserver	hadr_ddl_failover_execution_state	HADRON DDL Alter AG failover state
sqlserver	index_build_extents_allocation	Occurs when new extents are allocated during index building. Use this event to indentify how extents are used by an index.
filestream	filetable_store_item_moverename	Occurs when the FileTable store has processed a request to move or rename an item. Contains both the original item id name and the newly updated details.
Privileges
sqlserver	object_created	Occurs when an object was created by the CREATE statement. This event is raised two times for every CREATE operation. The event is raised when the operation begins and when the operation is either rolled back or committed. Add the nt_username or server_principal_name actions to this event to determine who altered the object.
sqlserver	object_deleted	Occurs when an object was deleted by the DROP statement. This event is raised two times for every DROP operation. The event is raised when the operation begins and when the operation is either rolled back or committed. Add the nt_username or server_principal_name actions to this event to determine who altered the object.
sqlserver	object_altered	Occurs when an object was altered by the ALTER statement. This event is raised two times for every ALTER operation. The event is raised when the operation begins and when the operation is either rolled back or committed. Add the nt_username or server_principal_name actions to this event to determine who altered the object.
sqlserver	hadr_db_manager_user_control	Change user status for a hadron controlled database.
Data Manipulation
sqlserver	cursor_prepare	Occurs when a statement that contains a cursor declaration is compiled into an execution plan but before the cursor is opened. Use this event to troubleshoot cursor performance issues.
sqlserver	cursor_open	Occurs when a statement is defined for a cursor the cursor properties are set and the cursor is populated. Use this event to troubleshoot cursor performance issues.
sqlserver	cursor_execute	Occurs when a cursor is created and executed. This event is used for troubleshooting cursor performance issues.
sqlserver	cursor_manager_cursor_begin	Cursor manager cursor begin
sqlserver	cursor_close	Occurs when a cursor is deallocated. This event is used for troubleshooting cursor performance issues.
sqlserver	object_created	Occurs when an object was created by the CREATE statement. This event is raised two times for every CREATE operation. The event is raised when the operation begins and when the operation is either rolled back or committed. Add the nt_username or server_principal_name actions to this event to determine who altered the object.
sqlserver	object_deleted	Occurs when an object was deleted by the DROP statement. This event is raised two times for every DROP operation. The event is raised when the operation begins and when the operation is either rolled back or committed. Add the nt_username or server_principal_name actions to this event to determine who altered the object.
sqlserver	object_altered	Occurs when an object was altered by the ALTER statement. This event is raised two times for every ALTER operation. The event is raised when the operation begins and when the operation is either rolled back or committed. Add the nt_username or server_principal_name actions to this event to determine who altered the object.
sqlserver	logreader_add_compensation_range	Occurs when the replication logreader encounters a partial rollback for a particular transaction.
sqlserver	rollback_tran_completed	Occurs when a ROLLBACK TRANSACTION request that was sent from a client application through the transaction management interface has completed.
sqlserver	rollback_tran_starting	Occurs when a ROLLBACK TRANSACTION request that was sent from a client application through the transaction management interface has started.
sqlserver	commit_tran_completed	Occurs when a COMMIT TRANSACTION request that was sent from a client application through the transaction management interface has completed.
sqlserver	commit_tran_starting	Occurs when a COMMIT TRANSACTION request that was sent from a client application through the transaction management interface has started.
sqlserver	group_commit_value_changed	Number of group commit waiters has changed
sqlserver	sp_cache_insert	Occurs when a stored procedure is inserted into the procedure cache. Use this event together with other sp_cache_ events to troubleshoot issues that are caused by the procedure cache.
sqlserver	query_rpc_insert_row	Inserting row in RPC rowset.
sqlserver	degree_of_parallelism	Occurs every time that SQL Server executes a SELECT INSERT UPDATE or DELETE statement. Use this event to monitor if a query is running in parallel.
sqlserver	alter_table_update_data	Occurs when an ALTER TABLE ADD column operation results in an UPDATE that affects every row. This event signals a potentially expensive DDL statement.
sqlserver	databases_bulk_insert_rows	Databases bulk insert rows due to BULK INSERT
sqlserver	query_rpc_insert_index_row	Inserting index row in RPC rowset.
sqlserver	query_rpc_locate_or_insert_row	Locating or inserting row in RPC rowset.
sqlserver	databases_bulk_insert_throughput	Databases bulk insert throughput in KB due to BULK INSERT
sqlserver	query_rpc_prepare_cmd_insert_row	Preparing InsertRow RPC command.
sqlserver	query_rpc_process_cmd_insert_row	Processing InsertRow RPC command.
sqlserver	full_update_instead_of_partial_update	Occurs when a DML operation that is intended to overwrite only part of a large column value is forced to rewrite the entire value. This happens if the tables heap or clustered index is being built online. Use this event to identify DML workloads that are running more slowly as a result of building an index online.
Exception Reporting
sqlserver	cdc_error	CDC Error Information
sqlserver	oledb_error	Occurs when an OLEDB Call to a remote provider returns an error. Use this event to view the Hresult returned from failed OLEDB Calls
sqlserver	error_reported	Occurs when an error is reported.
sqlserver	errorlog_written	Errorlog written
filestream	filetable_nso_error	Occurs when there has been an error during the operation of the FileTable namespace owner component. May also encompass informational traces.
sqlserver	background_job_error	Occurs when a background job has ended abnormally.
sqlserver	plan_guide_unsuccessful	Occurs when an execution plan is created for a query or batch without using the provided plan guide. Use this event to identify queries and batches with invalid plan guides. Use the sys.fn_validate_plan_guide function to detect errors in the plan guide.
sqlos	exception_ring_buffer_recorded	Occurs when an exception in the ring buffer is recorded.
sqlos	dump_exception_routine_executed	Dump exception routine executed
filestream	filetable_application_error	Reports extended information on specific errors that due to the limited error reporting of Win32 silently fail from the applications perspective.
sqlserver	broker_transmission_exception	Occurs when an exception is encountered during the processing of transmission objects
sqlserver	security_error_ring_buffer_recorded	Security error ring buffer recorded
sqlserver	hadr_wsfc_change_notifier_severe_error	HADR Windows Server Failover Clustering change notifier encountered a severe error and will terminate.

The following table lists the individual trace events belonging to each high level event group. Though depreciated, but a trace audit policy can still be created using 'Add Trace' button on 'Audit Policy Configuration' page.

Event ID	Event Name	Event Description
Query Tracking [query]
40	SQL:StmtStarting	Occurs when the Transact-SQL statement has started.
41	SQL:StmtCompleted	Occurs when the Transact-SQL statement has completed.
Login/Logout [loginout]
14	Audit Login	Occurs when a user successfully logs in to SQL Server.
15	Audit Logout	Occurs when a user logs out of SQL Server.
20	Audit Login Failed	Indicates that a login attempt to SQL Server from a client failed.
Transaction Tracking [transaction]
50	SQL Transaction	Tracks Transact-SQL BEGIN, COMMIT, SAVE, and ROLLBACK TRANSACTION statements.
181	TM: Begin Tran starting	Occurs when a BEGIN TRANSACTION request starts.
182	TM: Begin Tran completed	Occurs when a BEGIN TRANSACTION request completes.
183	TM: Promote Tran starting	Occurs when a PROMOTE TRANSACTION request starts.
184	TM: Promote Tran completed	Occurs when a PROMOTE TRANSACTION request completes.
185	TM: Commit Tran starting	Occurs when a COMMIT TRANSACTION request starts.
186	TM: Commit Tran completed	Occurs when a COMMIT TRANSACTION request completes.
187	TM: Rollback Tran starting	Occurs when a ROLLBACK TRANSACTION request starts.
188	TM: Rollback Tran completed	Occurs when a ROLLBACK TRANSACTION request completes.
191	TM: Save Tran starting	Occurs when a SAVE TRANSACTION request starts.
192	TM: Save Tran completed	Occurs when a SAVE TRANSACTION request completes.
Use of User Rights – Privileges [user-rights-use-priv]
132	Audit Server Principal Impersonation Event	Occurs when there is an impersonation within server scope, such as EXECUTE AS LOGIN.
133	Audit Database Principal Impersonation Event	Occurs when an impersonation occurs within the database scope, such as EXECUTE AS USER or SETUSER.
170	Audit Server Scope GDR Event	Indicates that a grant, deny, or revoke event for permissions in server scope occurred, such as creating a login.
171	Audit Server Object GDR Event	Indicates that a grant, deny, or revoke event for a schema object, such as a table or function, occurred.
172	Audit Database Object GDR Event	Indicates that a grant, deny, or revoke event for database objects, such as assemblies and schemas, occurred.
112	Audit App Role Change Password Event	Occurs when a password of an application role is changed.
102	Audit Statement GDR Event	Occurs every time a GRANT, DENY, REVOKE for a statement permission is issued by any user in SQL Server.
103	Audit Object GDR Event	Occurs every time a GRANT, DENY, REVOKE for an object permission is issued by any user in SQL Server.
Use of User Rights Data Manipulation Language (DML) [user-rights-use-dml]
114	Audit Schema Object Access Event	Occurs when an object permission (e.g. INSERT or UPDATE) is used, successfully or unsuccessfully.
Use of User Rights – Data Manipulation Language (DML) including SELECT
114	Audit Schema Object Access Event	Occurs when an object permission (SELECT) is used, successfully or unsuccessfully.
Use of User Rights- Data Definition Language [user-rights-use-ddl]
113	Audit Statement Permission Event	Occurs when a statement permission (such as CREATE TABLE) is used.
118	Audit Object Derived Permission Event	Occurs when a CREATE, ALTER, and DROP object commands are issued.
Account Admin [account-admin]
104	Audit AddLogin Event	Occurs when a SQL Server login is added or removed
105	Audit Login GDR Event	Occurs when a Windows login right is added or removed
106	Audit Login Change Property Event	Occurs when a property of a login, except passwords, is modified
107	Audit Login Change Password Event	Occurs when a SQL Server login password is changed. Passwords are not recorded.
108	Audit Add Login to Server Role Event	Occurs when a login is added or removed from a fixed server role
109	Audit Add DB User Event	Occurs when a login is added or removed as a database user (Windows or SQL Server) to a database
110	Audit Add Member to DB Role Event	Occurs when a login is added or removed as a database user (fixed or user-defined) to a database
111	Audit Add Role Event	Occurs when a login is added or removed as a database user to a database
Object Access [object-access]
128	Audit Database Management Event	Occurs when a database is created, altered, or dropped.
129	Audit Database Object Management Event	Occurs when a CREATE, ALTER, or DROP statement executes on database objects, such as schemas.
130	Audit Database Principal Management Event	Occurs when principals, such as users, are created, altered, or dropped from a database.
131	Audit Schema Object Management Event	Occurs when server objects are created, altered, or dropped.
134	Audit Server Object Take Ownership Event	Occurs when the owner is changed for objects in server scope.
135	Audit Database Object Take Ownership Event	Occurs when a change of owner for objects within database scope occurs.
152	Audit Change Database Owner	Occurs when ALTER AUTHORIZATION is used to change the owner of a database and permissions are checked to do that.
153	Audit Schema Object Take Ownership Event	Occurs when ALTER AUTHORIZATION is used to assign an owner to an object and permissions are checked to do that.
164	Object:Altered	Occurs when a database object is altered.
173	Audit Server Operation Event	Occurs when Security Audit operations such as altering settings, resources, external access, or authorization are used.
175	Audit Server Alter Trace Event	Occurs when a statement checks for the ALTER TRACE permission.
176	Audit Server Object Management Event	Occurs when server objects are created, altered, or dropped.
177	Audit Server Principal Management Event	Occurs when server principals are created, altered, or dropped.
178	Audit Database Operation Event	Occurs when database operations occur, such as checkpoint or subscribe query notification.
180	Audit Database Object Access Event	Occurs when database objects, such as schemas, are accessed.

A comprehensive list of events generated by Microsoft SQL Server can be found on the Microsoft Developer Network at http://msdn2.microsoft.com/en-us/library/ms186265.aspx