All we need is an easy explanation of the problem, so here it is.
Is it possible to see who changed a table (or a stored procedure) in a database when the recovery model is simple? E.g. length of data type…
If the recovery model is full will it be possible so see this in the log file?
Is it only with a trigger that one can log every access to a table or is there a build in option for this?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
You should have an extended event session in place but as a quick and dirty solution you can use the report below:
- Use DDL trigger to capture events such as "table change" or "stored procedure change"
See here an example: https://www.sqlshack.com/database-level-ddl-trigger-over-the-table/
It does not matter if your database is in simple, or full recovery model
- Also you could use SQL Server Audit feature, create database-level audit and specification
There are audit groups
DATABASE_OBJECT_CHANGE, use them to capture and log any create/alter/drop events (table, procedure, etc.) within given database
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂