April 27, 2005 at 12:46 pm
I have a table that has been losing rows intermittently. Seems to be random, but I put it down to user screwups. Today, the entire table vanished (totally gone from the database) and I have no explanation. On top of that, a table on a separate SQL instance lost all of the rows in it. (Same machine, different instance, and no relation between them.)
The table that disappeared was being actively read and written to. The table that lost all of its data was not a table that any of our apps wrote to, it was only read from. These issues may not be related, but who knows.
There is no method for deleting a table that is exposed to users. I am confident that it was not deleted by an employee. Something happend to it.
There are no errors in Event Viewer, nor in SQL's transaction logs.
Any advice anyone can give me would be greatly appreciated.
April 27, 2005 at 1:10 pm
The first thought that comes to mind is "dependencies"....are these tables dependent on any other object - stored procedures, triggers etc.. ??
What are the read/write access permissions granted to users ? Conversely - who has modify/delete rights ?
The good news is that it is always the same table that loses rows - how scary would it be if this happened to random tables - if you put down the disappearance of rows to "user screwups" that's where you should start - maybe there're some cascade deletes lurking somewhere..is the "disappearing table" linked to other tables ??
**ASCII stupid question, get a stupid ANSI !!!**
April 27, 2005 at 1:59 pm
stored procedures, yes. triggers, no. It is interesting, but the disappearing table has no code or procedures to delete rows. Only adding to it.
No linked tables either. It's really a simple setup. The table accepts new rows through a stored procedure called from a web app. The whole thing does nothing but add a record or 2. This isn't a huge multi-user system. And yet this table has dropped rows at least twice in a week, and then promptly vanishes! Driving me to drink!
Again, all code written to use this table only adds to it, there is no record-delete coded anywhere. And sure as heck nothing is written to blow away the entire table!
Also, ALL access is SQL running at owner level. I know, I know ...! It's not my design!
April 27, 2005 at 2:32 pm
You may have to run Profiler to trace it.
Or
Create a delete trigger on sysobjects and a delete trigger on that table to notify you when someone tries to delete the records or the entire table.
April 27, 2005 at 2:33 pm
Somebody is either screwing around with you or is inadvertantly doing this. First thing I would do is to lock down SQL Server. Eliminate any unused logins and users. Very carefully vet anyone with system level rights, removing anyone who doesn't actually need them. Remove the BuiltinAdmins and any other nonessential logins from the system Administrator group (add yourself there or you could lock yourself out!). Change the password for the SQL Login and grant it only the ability to execute the stored procs required. If you must grant the login direct access to the tables, at least make sure that it only has the permissions it actually needs.
You can use a tool like Log Explorer to go through the transaction log and find who made these changes and when, but if they used a common SQL login, you're out of luck.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
April 27, 2005 at 2:57 pm
What kind of application(s) are you running against this database? How do the applications connect to the server? Are you sure that you are protected against SQL Injection for instance?
April 27, 2005 at 10:09 pm
INSTEAD OF DELETE trigger is good idea.
You can also create dummy table with FK relation to PK of your table. This will block any attempt to delete rows from the table. And you can catch an error if application does it.
_____________
Code for TallyGenerator
April 28, 2005 at 9:06 am
I've had this problem in the past a few times. Depending on the privileges of the logins (whether you use integrated security or SQL security does not really matter), you may have a user using MS Access against the database table. Random sp_who2 checks or profiler will assist in identifying the culprit.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply