Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

audit DML activities Expand / Collapse
Author
Message
Posted Sunday, July 08, 2007 8:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 03, 2009 10:33 PM
Points: 12, 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.

 

 

Post #379796
Posted Monday, July 09, 2007 4:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 12:27 AM
Points: 344, Visits: 262

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,

Post #379905
Posted Tuesday, July 10, 2007 12:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 3:57 AM
Points: 312, Visits: 1,098

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.

Post #380173
Posted Tuesday, July 10, 2007 10:02 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, April 03, 2014 10:06 PM
Points: 6,621, Visits: 1,851

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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #380361
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse