Prerequisite
1. Enable Audit Object Access.
- Go to local computer policy (go to RUN, type gpedit.msc). If server manage by GPO, apply in GPO.
- Go to Computer Configuration > Windows Settings > Seurity Settings > Local Policies > Audit Policy > Audit Object Access
- Enable the policy (check the Success or/and Failure)
2. Grant right to SQL service account.
- Go to local computer policy (go to RUN, type gpedit.msc). If server manage by GPO, apply in GPO.
- Go to Computer Configuration > Windows Settings > Seurity Settings > Local Policies > User Right Assignment > Generate security audits
- Add SQL service account.
- Go to Registry HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Security\MSSQL$<InstanceName>$Audit\EventSourceFlags
- Change the value from 0 to 1.
Enable SQL Audit Log
1. Connect to SQL database with SQL Management tool.
2. Go to Security > Audits.
3. Right on Audit, select New Audit...
4. Configure audit settings. Select audit destination, Security log.
5. Right click on the newly created audit, select Enable audit.
