0

What is the best method for tracking changes or logging the queries done to a table by a specific user when the person is using Management Studio?

I'm using 2008 R2 Express Edition and want to specifically track a single user who logs in through Management studio and runs queries to make changes manually. I want to see what query was run and thus determine what was changed and how. I am not interested in restoring the information. I considered Change Tracking but read that it is not ideal for auditing as well I am unsure how to read the data, then I considered the Bulk-Logging option on the database however I then have to consider handling the log files which may grow huge as the database is used constantly by a web app. I am wondering if there is a more concise method to do what I want?

2
  • How are they authenticating to the server? Using a SQL login or Windows authentication?
    – joeqwerty
    Nov 8, 2012 at 16:18
  • windows auth, to which they are a member of the specific database's security permissions.
    – scape
    Nov 8, 2012 at 16:20

2 Answers 2

1

Change Tracking does not track the actual data that was changed.

Change Data Capture does track the data that was changed, but is an Enterprise Edition feature.

You can create your own audit tables and specify BEFORE triggers for the corresponding DML and data, to record whatever information you want.

Comparing Change Data Capture and Change Tracking
http://msdn.microsoft.com/en-us/library/cc280519%28v=sql.105%29.aspx

Change Data Capture
http://msdn.microsoft.com/en-us/library/bb522489%28v=sql.105%29.aspx

CREATE TRIGGER (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms189799%28v=sql.105%29.aspx

2

Starting with SQL Server 2008, there are 3 native auditing solutions - Change Tracking, Change Data Capture, and SQL Server Audit, but only one tracks the user who made the changes

Change Tracking doesn’t answer the “who”, “when”, and “how” questions. In addition, if there were multiple changes on a specific row, only the last one is shown. The feature just indicates whether the row has been changed or not. It shows the ID of the row changed, and the specific column that is changed. What this feature doesn’t provide are the details about the change. You can match the change information to the database snapshot and the live database to find out more about the changes, but this requires additional coding and still doesn’t bring all the information that might be needed for auditing. It doesn't track the queries executed either. As for reading the data, there are no built-in reports, you must use change tracking functions

Setting the database to Bulk-Logging recovery model does not provide the information about the queries executed against the database. it will however provide the info about who did what. To keep the online database transaction log file from growing huge, create transaction log backups periodically

Change Data Tracking also doesn't track who made the change and what code was executed. The same as with the SQL Server Change Tracking feature, the change information in SQL Server Change Data Capture is available through table valued functions

While Change Tracking shows only what was changed and whether the change was an insert, update, or delete, Change Data Capture shows the values inserted, deleted or updated for the modified rows. For updates, it shows both old and new values of the updated row

SQL Server Audit is the only feature that captures the user name who made the change. It also captures execution of SELECT and EXECUTE statements. The audited info can be stored in 3 types of files - a *.sqladuit file, application and security log and you can use the fn_get_audit_file function, Log File Viewer utility in SQL Server Management Studio, and Windows Event Viewer to read them

Also, there are 3rd party auditing tools that provide also built-in reporting, such as ApexSQL Comply

Disclaimer: I work for ApexSQL as a Support Engineer

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .