Log of adding a field

Question

Log of adding a field

Krasavita

SSC-Insane

Points: 23138
More actions
January 6, 2009 at 8:24 am

#129730

Is there way to find out who added a field in the table. Thank you

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

AFTER someone has already added it? maybe, but probably no. it's possible only under the following conditions:

database recovery model is set to FULL, not SIMPLE...then a third party log reading tool can read the log and find the ALTER TABLE command.

you can look at the default trace, which might have some info in it, here's an example:

[font="Courier New"]DECLARE @dbname SYSNAME

SET @dbname = DB_NAME() -- modify db name

/* ------------------------------------------------------------------------------- */

-- obtain file name for Default Trace

DECLARE @TraceFileName NVARCHAR(256)

SET @TraceFileName = (SELECT path FROM sys.traces WHERE is_default = 1)

-- get general name for Default Trace (remove rollover number)

SET @TraceFileName =

SUBSTRING (@Tracefilename, 1, (CHARINDEX ('\log_', @tracefilename) + 3)) + '.trc'

-- sample query: get info about recently added, deleted and modified

-- stored procedures in a database

SELECT

ev.name

,tr.StartTime

,tr.DatabaseID

,tr.DatabaseName

,tr.ObjectID

,tr.ObjectName AS 'Trace ObjectName'

,o.name AS 'Sysobjects Name'

,o.type_desc

,tr.ObjectType

/* see 'ObjectType Trace Event Column' in BOL to get a translation of these type codes */

,sv.subclass_name AS 'ObjectAbbrv'

,tr.LoginName

,tr.HostName

,tr.ApplicationName

FROM fn_trace_gettable(@TraceFileName, DEFAULT) tr

JOIN sys.trace_events ev

ON tr.eventclass = ev.trace_event_id

JOIN sys.trace_subclass_values sv

ON tr.eventclass = sv.trace_event_id

AND tr.ObjectType = sv.subclass_value

AND sv.trace_column_id = 28

LEFT JOIN sys.objects o

ON tr.ObjectID = o.OBJECT_ID

WHERE starttime > '2008-06-30 11:00'

AND databaseID = DB_ID(@dbname)

AND eventclass IN (46, 47, 164) -- object created, deleted or altered

AND eventsubclass = 1 -- only committed act

[/font]

Going forward, then you can add DDL auditing to each database you have:

[font="Courier New"]

CREATE TABLE [dbo].[DDLEventLog](

[EventDate] [datetime] NOT NULL,

[UserName] [sysname] NOT NULL,

[objectName] [sysname] NOT NULL,

[CommandText] [varchar](MAX) NOT NULL,

[EventType] [nvarchar](100) NULL

)

--

GO

CREATE TRIGGER [ReturnTBEventData]

ON DATABASE

FOR

CREATE_TABLE, DROP_TABLE, ALTER_TABLE

AS

DECLARE @eventData XML,

@uname NVARCHAR(50),

@oname NVARCHAR(100),

@otext VARCHAR(MAX),

@etype NVARCHAR(100),

@edate DATETIME

SET @eventData = eventdata()

SELECT

@edate=GETDATE(),

@uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),

@oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),

@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'VARCHAR(MAX)'),

@etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

IF @uname IS NOT NULL

BEGIN

INSERT dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType) VALUES

(@edate,@uname,@oname,@otext,@etype)

END

GO

ENABLE TRIGGER [ReturnTBEventData] ON DATABASE

--query to populate the original default values:

SELECT

sysobjects.[name] AS 'Table Name',

syscomments.colid AS seq,

syscomments.TEXT

FROM sysobjects

INNER JOIN syscomments ON syscomments.id = sysobjects.[id]

WHERE sysobjects.xtype IN('P','V','TR','IF''FN','TF')

ORDER BY sysobjects.[name],syscomments.colid[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

Thank you for your help, but my db is set to full, what 3rd party software are you refering? When I run your code I get an error:Invalid object name 'sys.traces'.

Lowell SSC Guru Points: 323546 More actions · Answer 3

yulichka (1/6/2009)
Thank you for your help, but my db is set to full, what 3rd party software are you refering? When I run your code I get an error:Invalid object name 'sys.traces'.

you posted in a sql 2005 forum, so I was assuming you are using SQL 2005; SQL2000 does not have any of the sys.views like sys.traces.

This is only relevant if you're talking SQL Server 2000 but RedGate produced a free tool called SQL Log Rescue. other Log Viewers typically cost money, you can search here on SSC for "Log Viewer" for more options.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

You might also reconsider your security model and the permissions people have if you are having an issue with something like a column being added to a table and no one admitting that they did it. That would tell me that you have someone with to much access.