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)