September 28, 2009 at 9:01 am
Hello, On my database server someone within my team deleted several heavily used stored procedures however, no one is taken responsiblity for it. Is there a way that I can run a process to audit to see who was logged into the database server and deleted the procs, or is it a lost cause?
I did a restore of the database for the last 5 days and I noticed that the stored procedures were remvoed as of Friday nights backup. Is there anyway I can find out since no one is coming forward with it?
This has happened before on the phyiscal server and with an audit of the server we found the person that was tampering with stuff so I was wondering if SQL had anything that can do the same.
September 28, 2009 at 9:07 am
You might be able to tell if the information is still in the default trace. If you've got SSMS and you've got it patched to at least SP2 (where Reports shows up), right click on the database, choose Reports, Standard Reports, and Schema Changes History.
K. Brian Kelley
@kbriankelley
September 28, 2009 at 9:14 am
thanks that worked, but I have a question about it. On the report for Schema Change, its showing me that all of the system stored procedures were dropped then created and the user generated procs were just dropped. What would cause that to happen?
September 28, 2009 at 9:27 am
A patch or service pack might cause system objects to drop and rebuild.
A user could do either one.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply