Blog Post

SQL Homework – June 2021 – SQL Audits

,

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)

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating