Microsoft SQL Server SQL Audit and Event Log Events

I Microsoft SQL Server SQL Audit and Event Log Events

Microsoft SQL Server SQ audit events cover categories such as account management events and application management events.

I.1 SQL Audit Events

SQL Audit Events map server-level, database-level groups of events and individual events.

The Audit action items can be individual actions such as SELECT operations on a Table, or a group of actions such as SERVER_PERMISSION_CHANGE_GROUP.

SQL Audit Events track the following three categories of Events:

Server Level: These actions include server operations, such as management changes, and logon and logoff operations.
Database Level: These actions include data manipulation languages (DML) and Data Definition Language (DDL).
Audit Level: These actions include actions in the auditing process.

Note:

In the table below the Target Type can be anything from Possible Target Types Values Associated With SQL Audit and Event Log Events.

Table I-1 SQL Audit Events

Source Event	Event Description	Command Class
`DATABASE_ROLE_MEMBER_CHANGE_GROUP`	Database Role Member Change Group	`ALTER`
`BACKUP LOG`	Backup Log	`BACKUP`
`ALTER RESOURCES`	Alter Resources	`ALTER`
`DELETE`	Delete	`DELETE`
`BROKER LOGIN`	Broker Login	`LOGIN`
`LOGOUT GROUP`	Logout Group	`LOGOUT`
`MUST CHANGE PASSWORD`	Must Change Password	`UPDATE`
`DROP MEMBER`	Drop Member	`DROP`
`DENY`	Deny	`DENY`
`SEND`	Send	`SEND`
`SELECT`	Select	`SELECT`
`SERVER_CONTINUE`	Server Continue	`RESUME`
`SERVER OPERATION GROUP`	Server Operation Group	`EXECUTE`
`INSERT`	Insert	`INSERT`
`EXECUTE`	Execute	`EXECUTE`
`SHOW PLAN`	Show Plan	`EXECUTE`
`SUCCESSFUL_LOGIN_GROUP`	Successful Login Group	`LOGIN`
`SERVER_ROLE_MEMBER_CHANGE_GROUP`	Server Role Member Change Group	`ALTER`
`ALTER TRACE`	Alter Trace	`ALTER`
`CREDENTIAL MAP TO LOGIN`	Credential Map to Login	`SET`
`FULL TEXT`	Full Text	`EXECUTE`
`TRACE AUDIT C2ON`	Trace Audit C2On	`AUDIT`
`BULK ADMIN`	Bulk Admin	`INSERT`
`TRACE AUDIT C2OFF`	Trace Audit C2Off	`NOAUDIT`
`VIEW SERVER STATE`	View Server State	`EXECUTE`
`SCHEMA_OBJECT_ACCESS_GROUP`	Schema Object Access Group	`ACCESS`
`ALTER CONNECTION`	Alter Connection	`ALTER`
`ALTER SETTINGS`	Alter Settings	`ALTER`
`ALTER SERVER STATE`	Alter Server State	`ALTER`
`EXTERNAL ACCESS ASSEMBLY`	External Access Assembly	`ACCESS`
`OPEN`	Open	`OPEN`
`AUDIT SHUTDOWN ON FAILURE`	Audit Shutdown On Failure	`NOAUDIT`
`AUDIT SESSION CHANGED`	Audit Session Changed	`AUDIT`
`BACKUP_RESTORE_GROUP`	Backup Restore Group	`RESTORE`
`SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP`	Server Object Ownership Change Group	`ALTER`
`AUTHENTICATE`	Authenticate	`AUTHENTICATE`
`DATABASE_OWNERSHIP_CHANGE_GROUP`	Database Ownership Change Group	`ALTER`
`REFERENCES`	References	`ACCESS`
`SERVER_STARTED`	Server Started	`STARTUP`
`DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP`	Database Object Ownership Change Group	`ALTER`
`SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP`	Schema Object Permission Change Group	`ALTER`
`IMPERSONATE`	Impersonate	`PROXY`
`CREATE`	Create	`CREATE`
`SERVER_STATE_CHANGE_GROUP`	Server State Change Group	`ALTER`
`TAKE OWNERSHIP`	Take Ownership	`ALTER`
`TRANSFER`	Transfer	`MOVE`
`CHANGE USERS LOGIN AUTO`	Change Users Login Auto	`ALTER`
`ADD MEMBER`	Add Member	`UPDATE`
`VIEW CHANGETRACKING`	View ChangeTracking	`EXECUTE`
`LOGIN FAILED`	Login Failed	`LOGIN`
`DATABASE_PRINCIPAL_CHANGE_GROUP`	Database Principal Change Group	`ALTER`
`DATABASE_OBJECT_CHANGE_GROUP`	Database Object Change Group	`UPDATE`
`DATABASE_MIRRORING_LOGIN_GROUP`	Database Mirroring Login Group	`LOGIN`
`ALTER`	Alter	`LOGIN`
`PASSWORD EXPIRATION`	Password Expiration	`EXPIRE`
`UPDATE`	Update	`UPDATE`
`NAME CHANGE`	Name Change	`ALTER`
`LOGOUT`	Logout	`LOGOUT`
`LOGIN SUCCEEDED`	Login Succeeded	`LOGIN`
`DATABASE_CHANGE_GROUP`	Database Change Group	`UPDATE`
`LOGIN_CHANGE_PASSWORD_GROUP`	Login Change Password Group	`UPDATE`
`RESET OWN PASSWORD`	Reset Own Password	`RESET`
`CHANGE USERS LOGIN`	Change Users Login	`ALTER`
`TRACE_CHANGE_GROUP`	Trace Change Group	`ALTER`
`FAILED_LOGIN_GROUP`	Failed Login Group	`LOGIN`
`TRACE AUDIT STOP`	Trace Audit Stop	`NOAUDIT`
`REVOKE`	Revoke	`REVOKE`
`CHANGE OWN PASSWORD`	Change Own Password	`UPDATE`
`CHANGE LOGIN CREDENTIAL`	Change Login Credential	`ALTER`
`RECEIVE`	Receive	`GET`
`AUDIT_CHANGE_GROUP`	Audit Change Group	`AUDIT`
`CHANGE DEFAULT LANGUAGE`	Change Default Language	`ALTER`
`CHANGE PASSWORD`	Change Password	`UPDATE`
`RESTORE`	Restore	`RESTORE`
`DATABASE MIRRORING LOGIN`	Database Mirroring Login	`LOGIN`
`REVOKE WITH CASCADE`	Revoke with Cascade	`REVOKE`
`DROP`	Drop	`DROP`
`SERVER_OBJECT_CHANGE_GROUP`	Server Object Change Group	`ALTER`
`VIEW_DATABASE_STATE`	View Database State	`EXECUTE`
`SERVER_PRINCIPAL_CHANGE_GROUP`	Server Principal Change Group	`ALTER`
`UNLOCK ACCOUNT`	Unlock Account	`UNLOCK`
`FULLTEXT_GROUP`	Fulltext Group	`EXECUTE`
`ENABLE`	Enable	`ENABLE`
`PASSWORD POLICY`	Password Policy	`UPDATE`
`REVOKE WITH GRANT`	Revoke With Grant	`REVOKE`
`DATABASE_PRINCIPAL_IMPERSONATION_GROUP`	Database Principal Impersonation Group	`PROXY`
`RESET PASSWORD`	Reset Password	`RESET`
`SUBSCRIBE QUERY NOTIFICATION`	Subscribe Query Notification	`SUBSCRIBE`
`SERVER_PRINCIPAL_IMPERSONATION_GROUP`	Server Principal Impersonation Group	`PROXY`
`APPLICATION_ROLE_CHANGE_PASSWORD_GROUP`	Application Role Change Password Group	`UPDATE`
`TRACE AUDIT START`	Trace Audit Start	`AUDIT`
`DATABASE OBJECT PERMISSION CHANGE GROUP`	Database Object Permission Change Group	`ALTER`
`SERVER PAUSED`	Server Paused	`PAUSE`
`DATABASE_OPERATION_GROUP`	Database Operation Group	`DML`
`ACCESS`	Access	`ACCESS`
`DATABASE_PERMISSION_CHANGE_GROUP`	Database Permission Change Group	`ALTER`
`UNSAFE ASSEMBLY`	Unsafe Assembly	`ACCESS`
`DENY WITH CASCADE`	Deny with Cascade	`DENY`
`DBCC_GROUP`	DBCC Group	`EXECUTE`
`BROKER_LOGIN_GROUP`	Broker Login Group	`LOGIN`
`CHECKPOINT`	Checkpoint	`SAVEPOINT`
`SERVER SHUTDOWN`	Server Shutdown	`SHUTDOWN`
`NO CREDENTIAL MAP TO LOGIN`	No Credential Map to Login	`SET`
`SCHEMA_OBJECT_CHANGE_GROUP`	Schema Object Change Group	`ALTER`
`CONNECT`	Connect	`CONNECT`
`GRANT WITH GRANT`	Grant with Grant	`GRANT`
`CHANGE DEFAULT DATABASE`	Change Default Database	`ALTER`
`DISABLE`	Disable	`DISABLE`
`SCHEMA_OBJECT_OWNERSHIP CHANGE_GROUP`	Schema Object Ownership Change Group	`ALTER`
`GRANT`	Grant	`GRANT`
`SERVER_PERMISSION_CHANGE_GROUP`	Server Permission Change Group	`ALTER`
`SERVER_OBJECT_PERMISSION CHANGE_GROUP`	Server Object Permission Change Group	`ALTER`
`DATABASE_OBJECT_ACCESS_GROUP`	Database Object Access Group	`ACCESS`
`DBCC`	DBCC	`EXECUTE`
`BACKUP`	Backup	`BACKUP`
`GLOBAL TRANSACTIONS LOGIN`	`Global Transaction Login`	`LOGIN`
`GLOBAL_TRANSACTION_LOGIN_GROUP`	`Global Transaction Login Group`	`LOGIN`
`VIEW`	`VIEW`	`EXECUTE`

I.2 Event Log Events

Event Log Events help you audit server-level, database-level and individual events.

These events consist of zero or more audit action items which can be either a group of actions (DATABASE_MIRRORING_LOGIN_GROUP) or individual actions (SELECT or REVOKE).

The Event Log Events track the following three categories of events.

Server Level: These actions include server operations such as management changes, and logon and logoff operations.
Database Level: These actions include data manipulation (DML) languages and Data Definition Language (DDL).
Audit Level: These actions include actions in the auditing process.

Table I-2 Event Log Events

Source Events	Event Description	Command Class	Target Type
`OP ALTER TRACE:STOP`	OP Alter Trace: Stop	`STOP`	`DATABASE`
`OP ALTER TRACE:START`	OP Alter Trace: Start (Event ID: 19033)	`START`	`DATABASE`
`OP ALTER TRACE:START`	OP Alter Trace: Start (Event ID: 19034)	`START`	`DATABASE`
`LOGIN FAILED: ONLY ADMINISTRATORS CAN CONNECT AT THIS TIME`	Login Failed: Only Administrators Can Connect At This Time (Event ID: 18450)	`LOGIN`	`DATABASE`
`LOGIN FAILED: ONLY ADMINISTRATORS CAN CONNECT AT THIS TIME`	Login Failed: Only Administrators Can Connect At This Time (Event ID: 18451)	`LOGIN`	`DATABASE`
`LOGIN FAILED: UNTRUSTED DOMAIN`	Login Failed: Untrusted Domain	`LOGIN`	`DATABASE`
`LOGIN SUCCEEDED: TRUSTED`	Login Succeeded: Trusted	`LOGIN`	`DATABASE`
`LOGIN SUCCEEDED: NON-TRUSTED`	Login Succeeded: Non-Trusted	`LOGIN`	`DATABASE`
`LOGIN SUCCEEDED`	Login Succeeded	`LOGIN`	`DATABASE`
`LOGIN FAILED`	Login Failed	`LOGIN`	`DATABASE`
`LOGIN FAILED: ILLEGAL USER NAME`	Login Failed: Illegal User Name	`LOGIN`	`DATABASE`
`LOGIN FAILED: SIMULTANEOUS LICENSE LIMIT`	Login Failed: Simultaneous License Limit	`LOGIN`	`DATABASE`
`LOGIN FAILED: WORKSTATION LICENSING LIMIT`	Login Failed: Workstation Licensing Limit	`LOGIN`	`DATABASE`
`LOGIN FAILED: SIMULTANEOUS LICENSE LIMIT`	Login Failed: Simultaneous License Limit	`LOGIN`	`DATABASE`
`LOGIN FAILED: SERVER IN SINGLE USER MODE`	Login Failed: Server in Single User Mode	`LOGIN`	`DATABASE`
`LOGIN FAILED: ACCOUNT DISABLED`	Login Failed: Account Disabled	`LOGIN`	`DATABASE`
`LOGIN FAILED: ACCOUNT LOCKED`	Login Failed: Account Locked	`LOGIN`	`DATABASE`
`LOGIN FAILED: PASSWORD EXPIRED`	Login Failed: Password Expired	`LOGIN`	`DATABASE`
`LOGIN FAILED: PASSWORD MUST BE CHANGED`	Login Failed: Password Must Be Changed	`LOGIN`	`DATABASE`
`OP ERROR: SERVER SHUT DOWN`	OP Error: Server Shut Down	`RAISE`	`DATABASE`
`OP ERROR: MIRRORING ERROR`	OP Error: Mirroring Error	`RAISE`	`DATABASE`
`OP ERROR: STACK OVER FLOW`	OP Error: Stack Over Flow	`RAISE`	`DATABASE`
`OP ERROR: COMMIT`	OP Error: Commit	`RAISE`	`DATABASE`
`OP ERROR: ROLLBACK`	OP Error: Rollback	`RAISE`	`DATABASE`
`OP ERROR: DB OFFLINE`	OP Error: DB Offline	`RAISE`	`DATABASE`
`OP ERROR: PROCESS VIOLATION`	OP Error: Process Violation	`RAISE`	`DATABASE`
`OP ERROR: RESTORE FAILED`	OP Error: Restore Failed	`RAISE`	`DATABASE`
`OP ERROR: RECOVER`	OP Error: Recover	`RAISE`	`DATABASE`
`OP ERROR: .NET FATAL ERROR`	OP Error: .NET Fatal Error	`RAISE`	`DATABASE`
`OP ERROR: .NET USER CODE`	OP Error: .NET User Code	`RAISE`	`DATABASE`
`NOTIFICATION SERVICE`	Notification Service	`RAISE`	`DATABASE`
`PASSWORD POLICY UPDATE SUCCESFUL`	Password Policy Update Successful	`UPDATE`	`POLICY`
`OP` modify: `START`	OP Modify: Start	`STARTUP`	`DATABASE`
`OP` modify: `STOP`	OP Modify: Stop	`SHUTDOWN`	`DATABASE`

I.3 Target Type Values for SQL Audit and Event Log Events

Target Type values associated with certain audit events

These can be any from the following list. See the Audit Event tables in this Appendix for references.

I.4 Possible Target Types Values Associated With SQL Audit and Event Log Events

The possible target type values can be types such as constraints.

Possible Target Types	Class_Type
`CONSTRAINT`	`F`
`DATABASE`	`DT`
`DATABASE`	`DN`
`KEY`	`DK`
`CONSTRAINT`	`UQ`
`USER`	`US`
`CATALOG`	`FC`
`ENDPOINT`	`EP`
`NOTIFICATION`	`EN`
`VIEW`	`V`
`TYPE`	`TY`
`TREE`	`XR`
`FUNCTION`	`FS`
`FUNCTION`	`FT`
`FUNCTION`	`FN`
`STOPLIST`	`FL`
`USER`	`WU`
`GROUP`	`WG`
`USER`	`WL`
`STORED PROCEDURE`	`X`
`USER`	`GU`
`RESOURCE`	`RG`
`FILTER`	`RF`
`ROLE`	`RL`
`TABLE`	`S`
`ASSEMBLY`	`AS`
`ROLE`	`AR`
`QUERY`	`AQ`
`USER`	`AU`
`CONSTRAINT`	`C`
`QUERY`	`PQ`
`BROKER PRIORITY`	`PR`
`PARTITION`	`PS`
`AGGREGATE`	`AF`
`KEY`	`AK`
`USER`	AL
`RULE`	`R`
Undocumented	AP
`FUNCTION`	`TF`
`DEFAULT`	`D`
`TRIGGER`	`TR`
`USER`	`SU`
`SERVICE`	`SV`
`STATISTICS`	`ST`
`SCHEMA`	`SX`
`SERVICE`	`BN`
`TABLE`	`U`
`ASSEMBLY`	`TA`
`SERVER`	`SD`
`SCHEMA`	`SC`
`SESSION`	`SE`
`ROLE`	`SG`
`USER`	`CU`
`CONTRACT`	`CT`
`USER`	`SL`
`DATABASE`	`DB`
`KEY`	`SK`
`AUDIT SPECIFICATION`	`DA`
`SYNONYM`	`SN`
`SERVER`	`SR`
`QUEUE`	`SQ`
`ROUTE`	`RT`
`CREDENTIAL`	`CD`
`CERTIFICATE`	`CR`
`SERVER`	`CO`
`PROVIDER`	`CP`
`SERVER`	`T`
`AUDIT SPECIFICATION`	`SA`
`USER`	`CL`
`USER`	`LX`
`KEY`	`MK`
`MESSAGE`	`MT`
`OBJECT`	`ON`
`OBJECT`	`OB`
`STORED PROCEDURE`	`P`
`PRIMARY KEY`	`PK`
`FUNCTION`	`PF`
`ASSEMBLY`	`PC`
`SERVER AUDIT`	`A`
`FUNCTION`	`IF`
`FUNCTION`	`IS`
`TABLE`	`IT`
`INDEX`	`IX`
`COLUMN ENCRYPTION KEY`	`CK`
`COLUMN MASTER KEY DEFINITION`	`CM`
`DATABASE CREDENTIAL`	`DC`
`EXTERNAL DATA SOURCE`	`ED`
`EXTERNAL FILE FORMAT`	`EF`
`SECURITY POLICY`	`SP`
`SEARCH PROPERTY LIST`	`FP`
`SEQUENCE OBJECT`	`SO`
`AVAILABILITY GROUP`	`AG`