Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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; 
share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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