Suppose you want to audit all failed deletions on the projects table in the company_operations database and for all new tables in the database. Use the object-specific delete option for the projects table and use default table for all future tables in the database. You must be in the object’s database before you execute sp_audit to set object-specific auditing options:
sp_audit "security", "all", "all", "fail"
For this example, execute:
use company_operations go sp_audit "delete", "all", "projects", "fail" go sp_audit "delete", "all", "default table", "fail" go
Example 1 Turns on auditing for role alterations:
sp_audit "alter", "all", "master", "pass"
Example 2 Turns on auditing for successful role creations:
sp_audit "alter", "all", "master", "on"
Example 3 This example turns off auditing of dropping roles:
sp_audit "drop", "all", "master", "off"
Example 4 Turns off auditing of granting roles:
sp_audit "grant", "all", "master", "off"
Auditing is performed using the grant or role audit option generating the AUD_EVT_UDR_CMD (85) event audit record.
Example 5 Turns on auditing of revoking rules:
sp_audit "revoke", "all", "master", "on"
Auditing is performed using the revoke or role audit option generating the AUD_EVT_UDR_CMD (85) event audit record.
