I have a custom eCommerce web site, using third-party web services, that is processing dues payments for our members. Twice in the last day a specific data row for 50,000 users is having two of the dollar amount columns change to $0 for all 50,000 rows. Just those two columns are updated, nothing else in the row changes.
We have reviewed our coding and SPs and can find nothing that would update those rows.
I have approximate times that the events occurred, how would I find out what was running on the SQL Server at the time that could have made the change? I have looked through the Windows Event and SQL Server logs and do not see anything out of the ordinary. So I'm assuming that the update is coming from an authorized user.
I realize I may not be able to see the past if I don't have logging set up properly. The SQL Server (12.0.5557.0) is very vanilla configuration. But any clue to looking at history or setting up for future logging would be greatly appreciated.
Best Answer
Brian,
Unfortunately the Windows event log and SQL log will not provide that information.
Fortunately there are several ways to audit a such activity. SQLShack has a good explanation on the various techniques and 3rd party tools
https://www.sqlshack.com/various-techniques-to-audit-sql-server-databases/
I had a similar situation at my work where I need to track some unintentional updates to a table that seemed to happen a couple of times a week.
I decided to use out of the box SQL Server auditing features. I was able to track down who, where and when certain DML changes were occuring..
You need to first setup a server audit then create a database audit..
You can tailor your database audit to "update" statements on your specific table.. Limiting the audit to your table and "update" statements will limit the amount of auditing taking place.
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification?view=sql-server-2017
You set up the server audit to log the activity to a file locally or somewhere on your network. Make sure the SQL Service account has write permissions to whatever location you choose.
You will have to use the function sys.fn_get_audit_file to read the audit file.
https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql?view=sql-server-2017
After you have solved the issue you can simply disable or delete the server and database audit.