Stored Procedures, who did change this stored procedure

Question

Stored Procedures, who did change this stored procedure

Mischa E.J. Hoogendoorn

Mr or Mrs. 500

Points: 558
More actions
April 28, 2009 at 8:14 am

#130299

Hello,
I've got a question... I want to see what sql or domain user has changed a stored procedure and when possible, from what machine.
Is this possible in sql 2005 enterprise?
Thanks in advance, and best regards,
Mischa E.J. Hoogendoorn

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

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

BJ Cummings-783681 SSC Enthusiast Points: 101 More actions · Answer 1

I would suggest setting up a DDL trigger and writing this to an audit table. If you are unsure how to implement this please let me know.

BJC

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 2

There is some info here, http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/, but you you do want to set up auditing if that is an issue.

By default, only a limited amount of information is captured.

Lowell SSC Guru Points: 323518 More actions · Answer 3

you can use the default trace, and hopefully find what happened;

here is aan example:

-- 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 eventclass in (46, 47, 164) -- object created, deleted or altered

and eventsubclass = 1 -- only committed act

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!

Mischa E.J. Hoogendoorn Mr or Mrs. 500 Points: 558 More actions · Answer 4

Thanks you very much! That was exactly where I was looking for !

Thanks! 🙂