[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

Auditing

I have 806 tables in a database. How to turn on select, insert, update, delete auditing on all table at once instead of selecting individual tables.
0
Salman_Ali
Asked:
Salman_Ali
2 Solutions
 
QPRCommented:
Could create a cursor
http://msdn.microsoft.com/en-us/library/ms180169.aspx
to loop through all the user tables
http://msdn.microsoft.com/en-us/library/ms187406.aspx

and turn on auditing for each
0
 
AnujCommented:
Audinting the SELECT statement is only supported in SQL Server 2008 Enterprise Edition.

If you are on SQL Server 2008 Enterprise Edition, you can use the inbuilt Auditing feature, It supports INSERT, UPDATE, DELETE, UPDATE and SELECT. If you are on any other SQL Server Version you must create custom DML triggers on each tables, still you cannot audit the SELECT statements.

The other option is to make use of SQL Server Profiler to capture All the select statement and write them to a output  table later you can query the Profile out put table, But this is not designed for Auditing since this have performance overhead!
0
 
parthmalhanCommented:
you can use sp_msforeachtable(in-built procedure) to iterate through all tables.

run the following command to see space used by each table in a database

EXEC sp_msforeachtable 'sp_spaceused ''?'''

for more detail search this on MSDN.
0
 
AnujCommented:
This Link will helps you for Auditing using DDL triggers.
0
 
Salman_AliAuthor Commented:
code would be option to provide
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now