Search code examples
sql-serversecurityuser-inputaudit

Audit Microsoft SQL Server table with many columns

I have a problem with getting this audit to work on my table.

I have a table with 197 columns, and i want to audit that. We have used this solution successfully before but the tables always had lesser columns.

The solution i have implemented is from this url at the bottom.

It just seems like it has something to do with that my table has so many columns. Does anyone have any input about this at all?

Pop Rivett SQL Server Audit

Solution

  • I have two ideas too many columns vs wrong ordinal position.

    The code in link is from 2006. So it was written for SQL Server 2005. Based on COLUMNS_UPDATED():

    The ORDINAL_POSITION column of the INFORMATION_SCHEMA.COLUMNS view is not compatible with the bit pattern of columns returned by COLUMNS_UPDATED.

    To obtain a bit pattern compatible with COLUMNS_UPDATED, reference the ColumnID property of the COLUMNPROPERTY system function when you query the INFORMATION_SCHEMA.COLUMNS view.

    CREATE TABLE tab(id INT, col1 INT, col2 INT, col3 INT,
                 col4 INT, col5 INT, col6 INT, col7 INT, col8 INT);

ALTER TABLE tab DROP COLUMN col1;
ALTER TABLE tab DROP COLUMN col5;

ALTER TABLE tab ADD col9 INT;
ALTER TABLE tab ADD col1 INT;

SELECT column_name,
  ordinal_position,
  COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),      
                 COLUMN_NAME, 'ColumnID') AS pos
FROM INFORMATION_SCHEMA.columns
 WHERE [TABLE_NAME] like '%tab%'
ORDER BY ordinal_position;

    SqlFiddleDemo

    Output:

    ╔══════════════╦═══════════════════╦═════╗
║ column_name  ║ ordinal_position  ║ pos ║
╠══════════════╬═══════════════════╬═════╣
║ id           ║                1  ║   1 ║
║ col2         ║                2  ║   3 ║
║ col3         ║                3  ║   4 ║
║ col4         ║                4  ║   5 ║
║ col6         ║                5  ║   7 ║
║ col7         ║                6  ║   8 ║
║ col8         ║                7  ║   9 ║
║ col9         ║                8  ║  10 ║
║ col1         ║                9  ║  11 ║
╚══════════════╩═══════════════════╩═════╝

    Results:

    ORIDINAL_POSITION -> no gaps, range 1-9
pos(aka ColumnId) -> gaps, range 1-11, 2 and 6 skipped

    Alternatively to check if column has chagned or not you can use UPDATE:

    UPDATE ( column )

    Returns a Boolean value that indicates whether an INSERT or UPDATE attempt was made on a specified column of a table or view. UPDATE() is used anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions.