SQL Homework – June 2021 – SQL Audits

Leave a comment

June 1, 2021 by Kenneth Fisher

Last month I had you work on Extended Events, so hopefully now you are a bit more comfortable with them. This month give a SQL Audit a try. If you already did during last month’s homework it won’t hurt you to try it again.

SQL Audits use the Extended Events technology but expose a few events that aren’t available to XE. I also personally find the GUI far easier to work with.

  • Create two SQL Audits using the wizard. (20 pts total)
    • Create two Audits and of course review the options available to you. (10 pts)
    • Create a Server Audit Specification tied to one of the above audits. (5 pts)
    • Create a Database Audit Specification tied to one of the above audits. (5 pts)
  • How do you specify that you want to capture all SELECT events for any table in the database? (5 pts)
  • For any user? (5 pts)
  • Run some test events that will hit your audit(s). For example if you’ve got a SELECT event run a few SELECTs that you expect to show up. And maybe a few you expect won’t show up. (10 pts)
  • Read from your file target. (5 pts) What permissions are required? (5 pts)
  • Enable and disable your audit(s) and audit specification(s). Use both the GUI and T-SQL. You might even run a few test events between to make sure the enable/disable worked the way you expect. (10 pts)
  • Create an audit using T-SQL. (25 pts)
  • Edit your audits. Try using both the GUI and T-SQL. Maybe change where the audit files go. Try adding or removing a few events. (15 pts)
  • Bonus: When would you use a SQL Audit instead of an Extended Event session? What are some events available to you in Audits that aren’t there for XEs? (2 pts per event you find)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,753 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: