Table Audit

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

You can use triggers on each table, or you can just profile and log that to a file/table.

Andy

Antares686 SSC Guru Points: 125444 More actions · Answer 2

SELECTs can really only be caught via Profiler or other query monitoring method. The others can be done via triggers

Ex.

CREATE TRIGGER tr_AuditUse_tblX ON dbo.tblX

FOR INSERT, UPDATE, DELETE

AS

INSERT INTO tblAudit (tblName) VALUES ('tblX')

and tblAudit would be nothing more than 3 columns

UNID int IDENTITY (1,1)

tblName VARCHAR(50) or if all tables names close in lenght use CHAR

TimeOf DATETIME default of GETDATE()

All are null not allowed.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

taiyeb_zakir SSC Eights! Points: 802 More actions · Answer 3

Thanks for the solution. I tried using SQL Profiler Objects Event. But it does not work.

Object:Opened and Object:Closed is supposed to work for SELECT, INSERT and DELETE. But it does not fire when I do SELECT, INSERT.

Object:created and Object:deleted works only for create and drop tables.

Please suggest which events should I select in the profiler.

Thanks

Taiyeb

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Sorry try instead TSQL SQL:StmtCompleted and you will see the queries. You can also use Sotred Procedure items to see when a Procedure is used.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)