Logging ALL changes

We are aware that NAV has a change log functionality that can track the changes made to tables & feilds defined in the Change Log Setup, However, if the changes are made through program, then the change log will not capture it.

We need ALL changes to be logged for audit reason, especially our partner often patches data through codeunit or report. They suggested using SQL Profiler to track all changes, but i read that this tool adds big overhead to SQL server, requires frequent maintenance and it is difficult to narrow down the changes (since the logs are kept in SQL statements). It does not seem to be the right tool for our purpose.

Are there other alternatives for logging all the changes, whether with an add-on product or coding?

Well, first of all I don’t think it is smart to log all kinds of changes, as this would create a tremendous amount of data, wasting lots of precious diskspace and probably degrading performance remarkably …

Anyway. SQL Server 2008 provides some “Change Tracking” features (not Profiler); see http://msdn.microsoft.com/en-us/library/cc305322.aspx
Maybe this could help you.


Thanks, Jörg. I will have a look at the link.

Yes i agree that logging all changes will mean excessive overhead. Unfortunately, when things go wrong and we could not track what happened, then the business users / auditors would start questioning why the system did not log all changes…

I think the most important thing is to set up appropriate user rights, roles & permissions; so users could only make changes they are supposed to do. Then excessive auditing should not be necessary …

I can only add to what Jorg said - if rights & permissions still is not enough, that means you do not trust your employees, but untrusted employee is easier to be fired than putting two security guys with bazookas behind him watching (and logging) his every step…

Well, i guess the management does trust the employees… until something happened.

This requirement came about because of an incident where a role permission was modified “unknowingly”. Users who used to be able to use certain functions suddenly reported that they encountered permission error, and after comparing the permission table with an old backup of the database, we discovered that the permission of a role had been modified. The business was not happy about it, accusing IT personnel modifying permission without prior approval from the business/management and causing disruption to the business operations. They demanded an explanation.

We checked the change log and could not find any records of the changes. Normal users do not have permission to modify the roles, and our IT staff claimed that none of them had changed anything on the permission. We knew that our vendor had done some customisation on the permission table, but changes made through program could not be tracked. So we are unable to identified what actually happened, and hence the management wants ALL changes to the important tables to be tracked.

Besides that, sorry to say, the management in my part of the world does seem to be more non-trusting than those from the West. [:|]

OK, but isn’t that another story? Why logging ALL changes (which was the original request)? Wouldn’t it be wise to just monitor/log the user rights, roles & permission changes? Plus those “important tables”?