SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


audit DML activities


audit DML activities

Author
Message
rahul rathi
rahul rathi
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 7

Hi,

I am new to sql server. I would like help from you gurus out their. I wanted to audit the activites (DML) on my database. I wanted the audit to achive the following

1] Audit specific tables for the DML activities.

2] capture the details of the username, time and system from which this query executed.

3] Can i get the changed and the previous values.

4] I don't want to use the triggers.

I did read in some articles this is achivable with the help of SQL-profiler and osql but don't know the exact steps. Could some one provideme with the step by step guide to get this done?

Please help,

Rahul.


Balmukund Lakhani-269523
Balmukund Lakhani-269523
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1858 Visits: 367

Without triggers it is not very easy. Triggers can meet your exact requirement.

As you mentioned you can use Profiler tool which is UI based Tool but again you cannot query without saving them to table, moreover you cannot get old value.

Contact me at bmlakhani@yahoo.com (Messenger) for further questions,


ianT
ianT
Right there with Babe
Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)

Group: General Forum Members
Points: 798 Visits: 1299

Why not use triggers? The DDL triggers are designed for this.

If you switch c2 audit on then you will hit most requiements and get a lot more besides. Problem with previous values I think.


K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (25K reputation)

Group: Moderators
Points: 25204 Visits: 1917

You mean DML triggers. DDL triggers monitor for changes to the schema and objects themselves.

As to the original question, there are two options:

  • Triggers on the specific tables
  • Server traces

Of these two options, triggers are probably the best fit. Server traces will tell you the query, who executed it, and when they executed it, but it can't return the information on what the original values were. That's going to require a trigger. Triggers have special tables that are used depending on the operation:

INSERT

  • inserted - Tells you what is being added.

UPDATE

  • deleted - Tells you what the original values were.
  • inserted - Tells you what the new values are.

DELETE

  • deleted - Tells you what is being deleted.

You're not going to get this information via any other built-in means.



K. Brian Kelley
@‌kbriankelley
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search