July 28, 2005 at 8:27 pm
Hello every body,
I have a strange problem and I hope that you will be able to help me. The other morning when w went to work, we noticed that there are 100 + store procedures are missing and the remain of the SPs are dated on that day date. after long research we found that all what happen to the 2000 SQL and nothing happen to the data or to the tables. However, we could not figure out what cause the SP to be deleted or change the creation date. We called Microsoft but they told us that they don't have any idea what happen. So we end by restoring only the SP from the backup tape for the day before. Everything running fine right now. But I really would like to know what happen, what caused it or how to trace the activities of the SQL server during that time. I will apprciate and idea or direction
Sincerely;
July 28, 2005 at 8:47 pm
A lot of time when a create procedure code is generated, the drop procedure code is included at the top to automatically remove the procedure if it exists. Naturally, when dropped and re-created, you'd see that day's date. Could someone have run a build script or something?
K. Brian Kelley
@kbriankelley
July 28, 2005 at 9:11 pm
Thank you Kelley for your response.
the answering to your question is no. No body ran a script to recreate the sp. Beside we are only four people whom have permission to access the server or create any SP and four of us were present when that symtoms occur.
any other suggestion
sincerely,
July 28, 2005 at 9:41 pm
If there was no data corruption, something had to have happened which dropped those stored procedures. Does any service accounts have sysadmin access or db_owner access to the given database? Does BUILTIN\Administrators still have sysadmin permissions and if so, are there any members of the local administrators group (Operating System level) other than you four?
K. Brian Kelley
@kbriankelley
July 28, 2005 at 9:57 pm
Dear Kelley,
we are the only four in the company whom have administrator permissions. the rest of the employees access the system via the in-house custom application with limited user access. the only thing we know that the symtoms happen at 8:26 am
Also,
sincerely,
July 28, 2005 at 10:04 pm
Then I understand why Microsoft can't explain what happened. If there was no data corruption, someone (or some account) had to have dropped those stored procedures. Without more auditing in place, it's impossible to perform any forensics after the fact.
K. Brian Kelley
@kbriankelley
July 29, 2005 at 10:41 am
Dear Kelley,
let's assume that some body dropped those SP (which is impossible) how can you explain the change of the creation date for the rest of SP. You may say he/she creats them by running a script to greate over 1500 + between 8:00 am to 8:26 am if that the case then we should have all SP re-create it without to miss any one!
What do you say?
Sincerely;
August 1, 2005 at 7:55 am
Just a hunch... I already built a script that basically drops all the sp one by one and recreates them. The goal of that script was to recreate all the missing dependencies betwen objects. Anyways if a script like that is ran and that the sp code is incomplete/broken, the sp won't be recreated, but then again I doubt you have 100 sp with failing code in it. There's always the possibility that someone created such a script and forgot that some sp can have 2+ rows in syscomments which could cause this problem.
Have you checked if you have any jobs or dts scheduled to run such a script?
August 1, 2005 at 8:24 am
Dear Remi,
We checked all the jobs and there are no one contins a script to drop and recreate SP. What I am looking for right now is kind of a hint of any undocumented SP or Table which contains a server trace and has more information than the Activity log.
Sincerely;
August 1, 2005 at 10:13 am
Depending on the state of your transaction logs you might be able to view the logs to see what happened. However, if they're coming in as dbo, that's not probably going to tell you who.
As for the dates, as indicated, a lot of folks write scripts which drop all the stored procedures and then recreates them. If there were errors during recreation or there were separate scripts, say one to drop them all and then multiple creation ones, it could have been one of the creation ones didn't get re-run.
K. Brian Kelley
@kbriankelley
August 1, 2005 at 11:22 am
Kelley,
We are very sure that non of us (we are only four people whom able to create and access the server physically and create or modify SP) generated script to recreate all the SP. if you thing one of the Job did not run then where is the Job. we review all the jobs and we could not find any job which drop and create even one SP.
any Idea?
Sincerely,
August 1, 2005 at 11:38 am
We can only offer guesses without more information. If this happens again you'll have to start a trace and see who's running that command. There's pretty much nothing else we can do to help in this case.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply