| Author |
Topic |
|
Ach
Starting Member
13 Posts |
 Posted - 2005-08-02 : 09:11:11
|
| Hi all, I face a problem as follows: We have an application runnig on SS2K.We log every delete of documents(from Archive table) in another table.Now it seems some of the rows have deleted strangely without any delete log by our application.We assumed there is somebody who has direct access to database and delete them manually(obviousely our app does not generate any log in this situation)But there is no people.We check that with admins many times. Does SQL Server itself deletes rows for any reason? How can I know what is happening?Do you think our app flaws somewhere? Thanks a lot for your attention. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-08-02 : 10:21:38
|
quote:
Originally posted by X002548
Must be the miracle engine..... Does everyone use sa to login in to the database?Brett
No we use the domain admin account rather than the sql admin account. By not specifying a user account, we find that we can mess up stuff much quicker. DanielSQL Server DBA |
 |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-08-02 : 10:29:03
|
| I am in a mood today.I personally like giving everyone the sa password installing the client tools on their machine and dropping a copy of SQL in 24 Hours.====================================================Regards,Sean RoussyGENERAL ADVICE FOR EVERYONE: Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-02 : 10:42:34
|
| If you used a TRIGGER then manual deletes would be logged too (malicious user with permissions could delete from Audit too I suppose)Kristen |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-08-02 : 11:02:25
|
quote:
Originally posted by Kristen
If you used a TRIGGER then manual deletes would be logged too (malicious user with permissions could delete from Audit too I suppose)Kristen
I do suppose if he used a trigger it may have been written to only handle one row at a time and if the delete was a bulk delete the trigger may have actually recorded something into the audit table but not everything. So in that example it would not be a malicious user who deleted the data.**Bulk delete meaning an SQL statement that deletes more than 1 row. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-02 : 11:16:12
|
| You may well be right, I read it that his application was doing the logging:"Now it seems some of the rows have deleted strangely without any delete log by our application."Kristen |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-08-02 : 11:44:38
|
quote:
Originally posted by Kristen
You may well be right, I read it that his application was doing the logging:"Now it seems some of the rows have deleted strangely without any delete log by our application."Kristen
Hehehe. Most of the time devs call Stored procs which contain business logic and do a lot of the work for the application, which in turn makes SQL Server part of the application too. At least thats the way I see it. DanielSQL Server DBAwww.dallasteam.com |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-02 : 13:17:25
|
| "At least thats the way I see it"Yeah well you're Dead Wrong. Absolutely. No shadow of a doubt. No more than 0.00000001% think that way.The rest use Dynamic SQL from the Application. Definitely. No doubt in my mind at all.Kristen |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-08-02 : 14:00:04
|
quote:
Originally posted by Kristen
"At least thats the way I see it"Yeah well you're Dead Wrong. Absolutely. No shadow of a doubt. No more than 0.00000001% think that way.The rest use Dynamic SQL from the Application. Definitely. No doubt in my mind at all.Kristen
Actually I was thinking that the rest hardcoded the SQL statements into the VB/C#/whatever. Who needs stored procs anyhow? The app will never change, right? Just write your commandtext as a string and set it to "Select * from [TABLE]" Why not just return everything from the database to the application as XML and let the application parse the data out as it sees fit. LOL. Wow, I'd hope that no one had that philosophy.DanielSQL Server DBAwww.dallasteam.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-02 : 14:33:58
|
| "Actually I was thinking that the rest hardcoded the SQL statements into the VB/C#/whatever"See? Wrong again. That is called "Dynamic SQL" "Select * from [TABLE]"This is just such a cool way to program. When your colleague adds seventeen TEXT columns to the table you'll have them right-there in your application ready to use. Whether you need them of not. I mean, imagine that your colleague creates a column containing a Movie showing all the Archive Data for the row - that is just so much more 21st Century than using an archive table. And your SELECT * will have it sat there in Memory ready for your application to use. Hey! I only just read your last sentence, Yeah! Spot on! Get the movie column data as XML then its device independent. You can have a web service so its always synchronised with the all the other sites in your organisation.Hang on I have to press F5 here ... <Ding> there we go ... ---> "Have a nice day!"Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-08-03 : 04:03:08
|
going back to the concern...  first check the following:1. triggers that may have been the culprit2. if no triggers, verify if your ini file (if not embedded) have been compromised (i hope you've encrypted it)3. if you can't verify, add a trigger in the table for deletes, check for the user_name (this will provide you with what account was used),log that in a separate table or file or directly email it to you,and/or rollback the transaction if the account is not supposed to be doing thatbtw, encrypt the trigger, so even if the user has access to that, he can't see where the info was logged or what's going on with the rollback HTH--------------------keeping it simple... |
|
|
|
|
Very strange behavior of SS2K...