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

Question

The inspiration for this question is a stored proc broke on me because it called another stored proc that inserted data in a table whose schema was completely altered.

The question is you have a table in a SQL Server database, and you don't know how it got there. You don't have any DDL triggers in place for custom audit information, and there is no record in your source control repository of the tables DDL. Using only SQL Server, what forensic data can you obtain about the table.

Myself, and whoever stumbles across this question in a similar situation, is not going to be helped by suggestions regarding version control and DDL triggers. Those are great go forward solutions, and there is plenty of info on this blog about those topics, if corporate politics allows us to implement those solutions. What I, and people in my situation really need is to be able to gather as many fragments of data as possible from SQL server, to combine with whatever other data we can come up with.

Answer 1

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

Answer 2

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')

Answer 3

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)