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

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.

Method 1

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

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

Method 2

  1. 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/

https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?view=sql-server-ver15

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply