SQL Server/Database Auditing

bbrown

Has anyone implemented SQL Audit on a system hosting NAV? if so, any issues?

kine

What you want to audit?

bbrown

Undecided at this time. Could be anything for which an audit action can be defined. More of a generic question right now. Just fishing if there are any "I did this and it broke my NAV system" responses.

rmv_RU

bbrown wrote:

Undecided at this time. Could be anything for which an audit action can be defined. More of a generic question right now. Just fishing if there are any "I did this and it broke my NAV system" responses.

Hi. My implementation is simple .
1. Create log table
2. Generate SQL triggers only for ledger tables.
You should use app_name() property to exclude Nav activity. For most Nav version app_name() is IN ('Microsoft Business Solutions-Navision client', 'Microsoft Dynamics NAV client', 'Microsoft Business Solutions-Navision', 'Microsoft Dynamics NAV','C/ODBC','C/FRONT','Navision Application Server', 'Microsoft Dynamics NAV Application Server','Navision VIP Application Server') .
You could use

exec('DBCC INPUTBUFFER ('+ @spid + ')') 

statetment to log modification query
Another useful properties are host_name() , system_user.

bbrown

I think you may have misunderstood my question. I'm not talking about inventing something. I'm talking about SQL Server Audit. (A feature of SQL 2008).