10

Looks like somebody has logged in and modified a Stored Procedure on our SQL Server database. Is there a way to find out the credentials of the person that has done that?

16

You can look at the default trace and figure this out easily. Try the examples from Jonathan Kehayias.

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/05/27/pass-dba-sig-default-trace-presentation-files.aspx

This is to show as an example and you need to look at the ALTER OBJECT event type.

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/09/29/what-session-created-that-object-in-tempdb.aspx

EDIT:

Code is courtesy of Jonathan Kehayias.

DECLARE @filename VARCHAR(255) 
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT gt.HostName, 
       gt.ApplicationName, 
       gt.NTUserName, 
       gt.NTDomainName, 
       gt.LoginName, 
       gt.SPID, 
       gt.EventClass, 
       te.Name AS EventName,
       gt.EventSubClass,      
       gt.TEXTData, 
       gt.StartTime, 
       gt.EndTime, 
       gt.ObjectName, 
       gt.DatabaseName, 
       gt.FileName, 
       gt.IsSystem
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
WHERE EventClass in (164) --AND gt.EventSubClass = 2
ORDER BY StartTime DESC; 
  • Stored procedures are not showing up in the results. Any ideas why? – Behrang Apr 12 '11 at 4:03
  • If SP are modified then they will show up. If you want, try creating a dummy SP & alter it and see if it shows up in the above report. – Sankar Reddy Apr 12 '11 at 4:11
  • Forget about my earlier comment. Figured it out. Thanks. – Behrang Apr 12 '11 at 4:12
  • Glad, you got it figure out :-) – Sankar Reddy Apr 12 '11 at 4:14
  • Be aware that this tracing system is deprecated according to MSDN docs. Microsoft recommends using their "Extended Events" paradigm instead: technet.microsoft.com/en-us/library/bb630354(v=sql.105).aspx – KeithS Mar 3 '17 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.