Database audit specifications

• jasonmorris

  SSCrazy

  Points: 2492

  More actions

  September 15, 2016 at 2:47 am

  #321474

  Hi,

  I have an audit spec which covers inserts, updates and deletes on certain tables i.e.

  ADD (DELETE ON OBJECT::[dbo].[T1] BY [dbo]),

  ADD (INSERT ON OBJECT::[dbo].[T1] BY [dbo]),

  ADD (UPDATE ON OBJECT::[dbo].[T1] BY [dbo]),

  Can anybody explain why the statement below gets audited with an action_id of 'UP' which is update

  please?

  thanks.

  select

  TABLE_CATALOG = s_cv.TABLE_CATALOG,

  TABLE_SCHEMA = s_cv.TABLE_SCHEMA,

  TABLE_NAME = s_cv.TABLE_NAME,

  COLUMN_NAME = s_cv.COLUMN_NAME,

  COLUMN_GUID = s_cv.COLUMN_GUID,

  COLUMN_PROPID = s_cv.COLUMN_PROPID,

  ORDINAL_POSITION = s_cv.ORDINAL_POSITION,

  COLUMN_HASDEFAULT = s_cv.COLUMN_HASDEFAULT,

  COLUMN_DEFAULT = s_cv.COLUMN_DEFAULT,

  COLUMN_FLAGS = s_cv.COLUMN_FLAGS,

  IS_NULLABLE = s_cv.IS_NULLABLE,

  DATA_TYPE = s_cv.DATA_TYPE, -- Used by Katmai+ clients

  TYPE_GUID = s_cv.TYPE_GUID,

  CHARACTER_MAXIMUM_LENGTH= s_cv.CHARACTER_MAXIMUM_LENGTH,

  CHARACTER_OCTET_LENGTH = s_cv.CHARACTER_OCTET_LENGTH,

  NUMERIC_PRECISION = s_cv.NUMERIC_PRECISION,

  NUMERIC_SCALE = s_cv.NUMERIC_SCALE,

  DATETIME_PRECISION = s_cv.DATETIME_PRECISION,

  CHARACTER_SET_CATALOG = s_cv.CHARACTER_SET_CATALOG,

  CHARACTER_SET_SCHEMA = s_cv.CHARACTER_SET_SCHEMA,

  CHARACTER_SET_NAME = s_cv.CHARACTER_SET_NAME,

  COLLATION_CATALOG = s_cv.COLLATION_CATALOG,

  COLLATION_SCHEMA = s_cv.COLLATION_SCHEMA,

  COLLATION_NAME = s_cv.COLLATION_NAME,

  DOMAIN_CATALOG = s_cv.DOMAIN_CATALOG,

  DOMAIN_SCHEMA = s_cv.DOMAIN_SCHEMA,

  DOMAIN_NAME = s_cv.DOMAIN_NAME,

  DESCRIPTION = s_cv.DESCRIPTION,

  COLUMN_LCID = s_cv.COLUMN_LCID,

  COLUMN_COMPFLAGS = s_cv.COLUMN_COMPFLAGS,

  COLUMN_SORTID = s_cv.COLUMN_SORTID,

  COLUMN_TDSCOLLATION = s_cv.COLUMN_TDSCOLLATION,

  IS_COMPUTED = s_cv.IS_COMPUTED,

  SS_XML_SCHEMACOLLECTION_CATALOGNAME = s_cv.SS_XML_SCHEMACOLLECTION_CATALOGNAME,

  SS_XML_SCHEMACOLLECTION_SCHEMANAME = s_cv.SS_XML_SCHEMACOLLECTION_SCHEMANAME,

  SS_XML_SCHEMACOLLECTIONNAME = s_cv.SS_XML_SCHEMACOLLECTIONNAME,

  SS_UDT_CATALOGNAME = s_cv.SS_UDT_CATALOGNAME,

  SS_UDT_SCHEMANAME = s_cv.SS_UDT_SCHEMANAME,

  SS_UDT_NAME = s_cv.SS_UDT_NAME,

  SS_UDT_ASSEMBLY_TYPENAME= s_cv.SS_UDT_ASSEMBLY_TYPENAME,

  SS_IS_SPARSE = s_cv.SS_IS_SPARSE,

  SS_IS_COLUMN_SET = s_cv.SS_IS_COLUMN_SET

  from

  sys.spt_columns_view s_cv

  where

  ( (@table_schema is null and s_cv.TABLE_NAME = @table_name)

  or s_cv.object_id = object_id(quotename(@table_schema) + '.' + quotename(@table_name)) )

  and (@column_name = s_cv.COLUMN_NAME or @column_name is null)

  order by 1, 2, 3, 7

• chatwithgeorgelucas

  SSC-Addicted

  Points: 446

  More actions

  September 15, 2016 at 3:47 am

  #1901050

  Even same question running in my mind. Looking for the solution.

  Mobile App Developer Istanbul[/url]
  Mobile App Design Agency Madrid[/url]

• achilles181

  Grasshopper

  Points: 21

  More actions

  July 16, 2018 at 11:52 pm

  #1997770

  Has this ever been resolved? I am trying to figure out the same thing?

• Johansql

  Old Hand

  Points: 334

  More actions

  June 21, 2021 at 11:28 am

  #3898783

  Hi, anyone seen this?

  I am trying to figure out the same thing?