Can we trace who changed the code for the SP ?

Question

Can we trace who changed the code for the SP ?

ansz5

Ten Centuries

Points: 1282
More actions
July 29, 2008 at 3:51 am

#70626

There was 1 common SQL Server login being used to execute the script. By mistake or intention the code in another SP is found to be modified. How can we track who and when made the changes ??
Pls suggest
Thanks !

Viewing 8 posts - 1 through 7 (of 7 total)

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

tedo SSCrazy Points: 2229 More actions · Answer 1

Have you tried SQL profiler, I am sure it should tell you who logged in and changed it, there should be an option in there that tells you who was logged in at the time it was changed.

Terry

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

In Profiler trace the Object altered/created/deleted events.

"Keep Trying"

ansz5 Ten Centuries Points: 1282 More actions · Answer 3

The code has been changed, we got to know.. later. Is there any way out , now...??

Profiler tracing would have helped in case it had been enabled to sav ethe traces.

Also , as it is a production server , Its is not advised to enable profiler tracing.. right ??

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

If you didn't have some form of tracing enabled, there's no way to tell who changed the proc.

As for profiling a prod server, via the GUI is a fairly bad idea, but tracing infrequent events via the server-side trace procedures (sp_trace*) is fine.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ansz5 Ten Centuries Points: 1282 More actions · Answer 5

Hi Gail ,

Thanks for guidance !

Can u pls tell about how and when to run the sp_trace * , and which are those infrequent events , which we should trace n take care..??

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Look up the sp_trace stored procedures in books online. That should give you a good overview of how they work. You can create a trace in profiler, then export the trace definitions to a script file. It's easier than writing it from scratch.

Unless you decide to trace things like lock acquired and or object accessed, you shouldn't have a problem.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 7

You have got to be carefull that no one share an account. Otherwise, your trial will be useless for someone who does it on purpose.

When you try to trace the activities, you had better send you traced data to some other place to avoid the hicker wiping out logs.

Normally, you can find out when your stored procedure is modified, you may try to check your eventviewer to find who was on this server in that period.