DRAG DROP
You administer a Microsoft SQL Server 2012 server that has a database named Contoso.
The Contoso database has a table named EmployeeSalary in a schema named
HumanResources.
You need to create a script that writes audit events into the application log whenever data in
the EmployeeSalary table is modified.
Which four Transact-SQL statements should you use? (To answer, move the appropriate
statements from the list of statements to the answer area and arrange them in the correct order.)

Answer: See the explanation.

Explanation:
Box 1: Use Master
Box 2:

Box 3: Use Contoso
Box 4:

Note:
* An audit must exist before creating a server audit specification for it. When a server audit
specification is created, it is in a disabled state.
* The general process for creating and using an audit is as follows.
1. Create an audit and define the target.
2. Create either a server audit specification or database audit specification that maps to
the audit. Enable the audit specification.
3. Enable the audit.
4. Read the audit events by using the Windows Event Viewer, Log File Viewer, or the
fn_get_audit_file function.
* (Box 2) Example:
Creating a server audit with a Windows Application log target with options
CREATE SERVER AUDIT HIPAA_Audit
TO APPLICATION_LOG
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = SHUTDOWN);
* Box 4 Example:
/*Creates a server audit specification called “HIPPA_Audit_Specification” that audits failed
logins for the SQL Server audit “HIPPA_Audit” created above.
*/
CREATE SERVER AUDIT SPECIFICATION HIPPA_Audit_Specification
FOR SERVER AUDIT HIPPA_Audit
ADD (FAILED_LOGIN_GROUP);
GO
— Enables the audit.
ALTER SERVER AUDIT HIPAA_Audit
WITH (STATE = ON);
GO