maintain audit trail of access to SQL server 2000 database

Is there any way to maintain audit trail of access to my SQL server 2000 database by any user ? I need to log the timestamp of any insert/update/delete to any record in a table within the database by the user.

Alex

Hi,

You need to enable Audit Schema Object Access Event for an audit, which is responsible for recording access to all schema objects including tables. Further you need to filer by database name, such that only events in a database you are interested in are recorded. Since you want to audit insert/update/delete for tables in database you need to place additional filters:

ObjectType = 'U' -- means user table

Permissions = 0x1a -- 0x2 | 0x8 | 0x10

You can further filer by table name (ObjectName column) if you wish.

The setup of the event described above can be easily done using SQL Server Profiler. Use "Column Filters" button to setup filters.

Let me know if you have further questions,

Ruslan

|||

ruslan ovechkin1 wrote:

Hi,

You need to enable Audit Schema Object Access Event for an audit, which is responsible for recording access to all schema objects including tables. Further you need to filer by database name, such that only events in a database you are interested in are recorded. Since you want to audit insert/update/delete for tables in database you need to place additional filters:

ObjectType = 'U' -- means user table

Permissions = 0x1a -- 0x2 | 0x8 | 0x10

You can further filer by table name (ObjectName column) if you wish.

The setup of the event described above can be easily done using SQL Server Profiler. Use "Column Filters" button to setup filters.

Let me know if you have further questions,

Ruslan

After enabling this, is it possibel to take report for the same ?

Vishal