Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Dear All,
I want to know if there is any way by which i can maintain a log of changes that takes place on the tables. If there is any then i want to know how is it done.

The situation is that before some days i added a COLUMN in table. But now i am not able to find the same COLUMN in that table. I know that someone has deleted that column by using ALTER query, but dont know who did it and i want to know it. Is it possible to know that who actually executed that query? can i get the IP of the machine from where the query might have been executed.

I am using SQL SERVER 2008 R2. and the database is on the local server which is accessible to all the people in LAN.
Posted
Comments
StianSandberg 19-Jul-12 6:43am    
good question.. As far as I know you can't. But I'll bookmark this question to see if there are any good solutions here..
Logging every executed sql would take up a lot of space in your database..

Have a look at SQL Server Audit Action Groups and Actions[^] - particularly DATABASE_OBJECT_CHANGE_GROUP.

This will allow you to monitor who is dropping the column.

Best regards
Espen Harlinn
 
Share this answer
 
Comments
Sandeep Mewara 22-Jul-12 2:55am    
Good one! 5!
Espen Harlinn 22-Jul-12 4:30am    
Thank you, Sandeep :-D
Sergey Alexandrovich Kryukov 22-Jul-12 14:41pm    
Good answer, a 5.
--SA
Espen Harlinn 22-Jul-12 14:46pm    
Thank you, Sergey :-D
Hi,

check this.
http://www.red-gate.com/products/sql-development/sqlsourcecontrol/[^]

this may solve the conflicts.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900