About auditing

• asspipi

  Grasshopper

  Points: 15

  More actions

  July 25, 2010 at 3:55 am

  #225379

  Hi,

  how can i use SSMS to see when only a specific field to changed in a table?

  Thanks

• tomaz.tsql

  Grasshopper

  Points: 11

  More actions

  July 25, 2010 at 1:46 pm

  #1197964

  what do you want to see?

• nary

  SSCrazy

  Points: 2869

  More actions

  July 25, 2010 at 1:53 pm

  #1197965

  you can't directly see when the particular feild has been changed , to do that u have to create the auditing trigger (DDL) then u can capture what /who/when has changed to any object

  HTH

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  July 25, 2010 at 4:49 pm

  #1197978

  As a beginning read the following article:

  http://msdn.microsoft.com/en-us/library/dd392015.aspx

  Here is an extremely brief abstract of the granularity of tasks::

  Also, SQL Server 2008 allows granular definition of audit criteria. Audits can be scoped to individual tables, to specific DML actions (for example, DELETE vs. SELECT) and to specific principals. This granularity can reduce the volume of audit data that must be stored and analyzed to meet a specific set of requirements.

  in addition the article explains how to use SSMS to create an audit.

  It is a very significant beginning to understand auditing in SQL 2008 which has been vastly improved and modified when compared to earlier versions of SQL Server

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• Amit kulkarni-491236

  Default port

  Points: 1492

  More actions

  July 26, 2010 at 7:14 am

  #1198176

  Using DDL Trigger is the best option. Create an audit table and an audit trigger on that AuditTable at database level. You can capture all the schema changes in the audit table at database level.

  Plz find the script below. Execute this script in the DB for which you want to audit and all events will be captured.

  -- Audit Table

  CREATE TABLE [dbo].[DBA_AuditDDLEvents](

  [lsn] [int] IDENTITY(1,1) NOT NULL,

  [posttime] [datetime] NOT NULL,

  [eventtype] [sysname] NOT NULL,

  [loginname] [sysname] NOT NULL,

  [schemaname] [sysname] NOT NULL,

  [objectname] [sysname] NOT NULL,

  [targetobjectname] [sysname] NOT NULL,

  [eventdata] [xml] NOT NULL,

  [TSQL] [nvarchar](4000) NULL,

  CONSTRAINT [PK_AuditDDLEvents] PRIMARY KEY CLUSTERED

  (

  [lsn] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  GO

  -- Trigger

  CREATE TRIGGER [Trg_Audit_DDL_Events] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS

  AS

  set nocount on

  DECLARE @eventdata AS XML;

  SET @eventdata = eventdata();

  INSERT INTO dbo.dba_AuditDDLEvents(

  posttime, eventtype, loginname, schemaname,

  objectname, targetobjectname, eventdata,TSQL)

  VALUES(

  CAST(@eventdata.query('data(//PostTime)') AS VARCHAR(23)),

  CAST(@eventdata.query('data(//EventType)') AS sysname),

  CAST(@eventdata.query('data(//LoginName)') AS sysname),

  CAST(@eventdata.query('data(//SchemaName)') AS sysname),

  CAST(@eventdata.query('data(//ObjectName)') AS sysname),

  CAST(@eventdata.query('data(//TargetObjectName)') AS sysname),

  @eventdata,@eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)') );

  GO