How to know who made the changes in Table structure

Question

Post reply

How to know who made the changes in Table structure

AJ07

Ten Centuries

Points: 1334
More actions
December 21, 2010 at 9:37 am

#244645

Hi,
I want to know who (which user/login) has made the changes in table structure. e.g. some new columns are added in one of our DB's table and I am trying to identify the user who made the changes.
I tried to search but m getting the results where i am able to know what was the last change in the table but unable to know who made the change.
Please help.
Thanks in advance 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Two options are:

1. Have Auditing enabled

2. Ask around to find out who changed it.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 2

If the default trace is enabled (default is on) then you can use the script HERE [/url]to read through that and find out possibly.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

David Benoit (12/21/2010)
If the default trace is enabled (default is on) then you can use the script HERE [/url]to read through that and find out possibly.

That is an excellent recommendation.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 4

CirquedeSQLeil (12/21/2010)
David Benoit (12/21/2010)
If the default trace is enabled (default is on) then you can use the script HERE [/url]to read through that and find out possibly.
That is an excellent recommendation.

Thanks! Learned through much pain. 🙂

The only downside to relying on the default trace is that it rolls over and you will eventually lose that information so, if you have a lot of activity, i.e. tempdb table creates, drops you will soon find that information gone. There are ways to save the data off, etc or create another trace that will save those changes not made in tempdb but that is another subject. 🙂

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot