CODEWITHSUNDEEP
sqlsql-server
Muhammad Sharjeel Ahsan
Muhammad Sharjeel Ahsan

Reputation: 672

find out who added new column in table

I have a very critical database with live customer data, we actually maintain this database. Someone have created in a critical table, a new column. I need to find out who created this column.

I am using SQL Server2005

This is a very critical issue. I have to answer to administration so quickly so any answer in this regard is very helpful.

Very very thanks in advance.

Upvotes: 1

Views: 2652

Answers (3)

Martin Smith
Martin Smith

Reputation: 453327

Depending on when the change occurred you may be able to determine this from the default trace (requires you to have not disabled it and for it to not have rolled over 5 times and deleted the trace file in the meantime).

See my answer to Determining how a schema change occurred? on the DBA site for additional details.

Upvotes: 4

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Using the EventData() Function with DDL triggers Create the event log table

--CREATE TABLE EvtLog  
(
PostTime DATETIME,
LoginName NVARCHAR(100),
EventType NVARCHAR(100), 
TSQLCommand NVARCHAR(2000)  
)
GO
--Create the DDL trigger
CREATE TRIGGER trPreventTblChange 
ON DATABASE
FOR ALTER_TABLE
AS
DECLARE @Data XML
SET @Data = EventData()
INSERT EvtLog (PostTime, LoginName, EventType, TSQLCommand) 
VALUES
(GETDATE(),
CONVERT(NVARCHAR(100), CURRENT_USER),   
@Data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),  
@Data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
GO

More info about DDL_TABLE_EVENTS

Upvotes: 1

Ken White
Ken White

Reputation: 125708

There's no way to do that after the fact, unless you've pre-configured the DB to do so.

This question can provide more information. There's also a link here that provides some more details.

Upvotes: 2

Related Questions