How to see who made a change to a table in SQL Server

Posted on

Question :

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?

Answer :

You should have an extended event session in place but as a quick and dirty solution you can use the report below:

enter image description here

  1. Use DDL trigger to capture events such as “table change” or “stored procedure change”

See here an example:

It does not matter if your database is in simple, or full recovery model

  1. Also you could use SQL Server Audit feature, create database-level audit and specification
    There are audit groups SCHEMA_OBJECT_CHANGE and DATABASE_OBJECT_CHANGE, use them to capture and log any create/alter/drop events (table, procedure, etc.) within given database

Leave a Reply

Your email address will not be published. Required fields are marked *