What sort of audit information is stored by sql server when a CREATE TABLE or ALTER TABLE operation is performed?

Unfortunately, you have no way to reconstruct what happened, except: if the DBAs have traces running and the history. SQL Server 2005 has a default trace and there is the "blackbox" trace, but these are used to find out what happened right before a crash if you have FULL recovery model, then you can try a log viewer tool ( free Red Gate one ) depending on activity and your security model, you could also check the Windows Security Log to see who logged in with DDL privileges the SQL event logs may also have information. For example, if you have enabled the trace flag that logs "permission" errors, then you can see when it started.

Or the person who made the change may have generated other exceptions that are logged you could ask those with rights. It could be a genuine mistake (eg thought they were in dev).

Unfortunately, you have no way to reconstruct what happened, except: if the DBAs have traces running and the history. SQL Server 2005 has a default trace and there is the "blackbox" trace, but these are used to find out what happened right before a crash if you have FULL recovery model, then you can try a log viewer tool (free Red Gate one) depending on activity and your security model, you could also check the Windows Security Log to see who logged in with DDL privileges the SQL event logs may also have information. For example, if you have enabled the trace flag that logs "permission" errors, then you can see when it started.

Or the person who made the change may have generated other exceptions that are logged you could ask those with rights. It could be a genuine mistake (eg thought they were in dev).

The following query got me the create and last modify time. SELECT * FROM sys. Objects WHERE object_id = OBJECT_ID(N'dbo.

TbTableInQuestion') AND type in (N'U').

You should be able to find the creation of the table in the transaction log. I have never used any of the viewer tools here, and I couldn't tell you how to search back in time for this, but everything happens in a transaction, so it's gotta be logged -- even the system tables...

I was able to get create and modify time from sys.objects. I doubt the transaction log for this is still around as it is over a month old, but I will ask our sysadmin if it does exist. – Justin Dearing Jun 12 '09 at 1:54 Unless it's SIMPLE recovery model... – gbn Jun 12 '09 at 3:45.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.