Database Object Auditing

[DBAWinnipeg]

Good Afternoon all

I'm trying to create a trigger to fire off of the sysobjects system table that will send an added, updated, or deleted row to an audit table I created...

I get this error

CREATE TRIGGER permission denied on object 'sysobjects', database 'CRMPRD', owner 'dbo'.

Now I kinda figured before hand that that was going to happen but i guess I was hoping it wouldn't... anyone have a work around??



Thanks in advance!!!

Colin in da 'Peg

 

[nigelrivett]

Can't be done.

Have a look at this if you want to monitor schema changes

http://www.nigelrivett.net/DMOScriptAllDatabases.html

Or better - this so you don't have to.

http://www.nigelrivett.net/SQLServerReleaseControl.htm

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[TonyKhela]

We use Lumigent's Entegra for Auditing DML, DCL type changes (ie any INSERTS, UPDATES, DELETES GRANT, DENY, SELECTS ETC).
Its a really 'easy to setup' auditing tool that Keeps a record in a separate SQL database of all activity.

http://www.kccltd.com/product/entegra.asp

Hope it helps

TK