I'm following this guide to create table audit for each table. However, I have a question: What if we have >100 tables. Is there any short hand that can create audit table for ALL tables in one step?
Yes, enable it for the entire database instead:
These are from the link you posted.
My understanding database/server level audit can only capture actions like login, permission change, etc, but not table access (CRUD). Am I correct?
I wonder in such case we should run a short script retreiving all tables and create audit spec on each of them.
CREATE DATABASE AUDIT SPECIFICATION MSSQL_Table_Specification FOR SERVER AUDIT MSSQL_Table_Audit ADD (UPDATE ON <table> BY <schema> ) WITH (STATE = ON) ;
Is it possible? Thanks
Another hard question from customer, would database audit above impact DB performance?
Auditing absolutely will impact performance, especially if its a heavily used database/table/etc and then even more so if you're writing audits to a slow disk.
Best Practices -Write audit logs to a centralized location -To facilitate processing of the audited data, load the logs into a database -Use a file as a target for optimal performance -Use targeted auditing to minimize the collected data and better performance -When writing to the Windows logs, ensure that the roll-over policy of the Windows Logs, coincides with that of your audit strategy - See more at: http://www.sqlshack.com/understanding-sql-server-audit/#sthash.71JR77T3.dpuf
As for your previous question, it's best to go to a SQL forum to ask that as I'm not a DBA.
It's chicken and egg problem... With audit logging (and also Splunk), we can have more insight what impact DB performance on the other hand. So probably the impact of introducing audit is a positive trade-off. I hope my customer would understand this.
I verified it myself. We can indeed create audit spec on schema insteawd of each table to quickly add auditing on all tables. Defining all actions including select, insert, update, delete are still required.
ALTER DATABASE AUDIT SPECIFICATION MSSQL_Table_Specification FOR SERVER AUDIT MSSQL_Table_Audit ADD (select ON SCHEMA::dbo BY dbo), ADD (insert ON SCHEMA::dbo BY dbo), ADD (update ON SCHEMA::dbo BY dbo), ADD (delete ON SCHEMA::dbo BY dbo) WITH (STATE = ON) ;