The Configuring Audits for SQL Server module provides you with the instructions and devices to develop your hands-on skills in the following topics.

  • Managing server-level audit
  • Managing database-level audit
  • Working with SQL Server audit log

Lab time: It will take approximately 60 minutes to complete this lab.

Exam Objectives

Three exam objectives are covered in this lab.

  • Create a server-level audit
  • Create a database-level audit
  • Query the SQL Server audit log

Exercise 1 - Managing Server-Level Audit

The auditing feature in SQL Server 2016 monitors, tracks, and records all server-level and database-level events. You can create server-level and database-level audit objects in the database. In this exercise, you will create a server-level audit and server audit specification.

Exercise 2 - Managing Database-Level Audit

To create a database-level audit, you must first create an audit object and define the target. Then, you must create the database audit specification and map it to the audit object. Finally, you must enable the audit.

In this exercise, you will create a new database audit specification using SQL Server Management Studio. You will also alter the database and add the DELETE audit action type using Transact-SQL.

Exercise 3 - Working with SQL Server Audit Log

You can view audit logs in SQL Server using the Log File Viewer. However, using sys.fngetaudit_file function, you can read the audit file data in SQL Server Management Studio.

In this exercise, you will execute sys.fngetaudit_file function and view the SQL Server audit logs.

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.