Just Learned - How to find out who modified an object in SQL Server

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

How to find out who modified an object in SQL Server

Jun 7 2012 12:00AM by Latheesh NK

A simple method to find out who altered/modified an object in SQL server using trace.

SELECT I.*
FROM
sys.traces T 
CROSS Apply ::fn_trace_gettable(T.path, T.max_files) I 
Join sys.trace_events E On I.eventclass = E.trace_event_id 
Where T.id = 1 And
E.name = 'Object:Altered' and ObjectName like '%UAC_AdminAccess%'

Read More.. [32134 clicks]

Published under: SQL Server Tips · · · ·

Previous: Data Warehouse: Difference between "slicing" and "dicing"
Next: ASP.Net button not firing when manipulating the DOM in a content page

Latheesh NK

55 · 4% · 1125

Liked

Knew

Learned

Incorrect

Interesting

Forgotten


Post this to my facebook wall
Tweet this
Like This
I knew this before
Thanks, I just learned it from you
Sorry, but this is not correct
Interesting, but not my area
I knew it but i have forgotten it


	Submit

4 Comments

Nice tip, but two things come to mind:

sys.traces is marked for deprecation
I believe this would return values as long as the necessary information is available in the default trace. Once the default trace overflows and the information is overwritten, this query would no longer yield the required resutls

The safe approach here would be to have DDL triggers defined on objects of interest.

commented on Jun 7 2012 1:39PM

Nakul Vachhrajani

4 · 33% · 10585

Yes, this only showed recent modifications from the last hour or so. It didn't show modifications from 8 - 10 hours ago.

commented on Jun 7 2012 1:46PM

ErikEckhardt

65 · 3% · 887

I agree with Nakul. I observed this before sometime when I would like to trace one event which I fired.

commented on Jun 7 2012 11:17PM

Hardik Doshi

20 · 9% · 2839

If you are not interested in seeing all logs , and interested in last entry only, i mean who has modified object last, then i think SYS.OBJECTS is the best option to go with.

commented on Sep 24 2012 5:26AM

Manoj Bhadiyadra

153 · 1% · 315

Your Comment

Sign Up or Login to post a comment.

"How to find out who modified an object in SQL Server" rated 5 out of 5 by 11 readers