Howzit .
SQL doesn't keep these changes in a log as such . You have to design a strategy that will keep the changes for you and when it occurs .
You can use SQL profiler , hence the performance impact - so becarefull what events and couters you choose and also if you can design something that will keep all the entries in your syscacheobjects joined to the sysprocesses table in master you should catch the offender .
I have tried to catch a bugger dropping indexes during prod hours by creating a trigger on the syscacheobjects system table but with no -avail ..so I am also in the situasion ..I will keep you informed if I make any progress ...
To all other SQL fundies out there , give your comments , there is two of us now that would like you input in this regard .
check out DB Ghost for a solution to database change management. For an excellent read check out this http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf
regards,Mark Baekdalwww.dbghost.com
+44 (0)208 241 1762Living and breathing database change management for SQL Server
Lumigent Log Explorer is another tool that you can use to capture this type of infoirmation. It can read and interpret the transaction log files and there is an option to capture user information along with the log entries. In this case, you can use it to look at the syspermissions table.
http://www.lumigent.com/products/le_sql.html
ApexSQL has a good tool read the SQL transaction log. You can capture who did what, when and can audit data and DDL structure. I have found it very cost effective compared to what else is out there.
http://apexsql.com/sql_tools_log.htm
