CODEWITHSUNDEEP
sql-server
georgesmith
georgesmith

Reputation: 3

Why are the database rows are getting deleted automatically sql 2008

Everyday, some of my database rows are getting deleted automatically.

Even the log files are getting deleted, so I am unable to check who deleted those files.

I dont understand what to do.

Upvotes: 1

Views: 2325

Answers (2)

Rory
Rory

Reputation: 41927

Run SQL Profiler against the DB capturing all RPC Completed and SQL BatchCompleted events and review it to find whatever is performing the deletes.

Upvotes: 1

Chris Gessler
Chris Gessler

Reputation: 23123

If the SQL server is pre-production, you could just yank all delete rights to the target table and wait to see who complains. If deletes are not allowed on this table anyway, even in production, then it would be a good idea to restrict that functionaity moving forward.

Beyond that, try adding a delete trigger to the table to do auditing. You can get the source IP address, logged in user info, etc. You can even rollback the delete if needed.

Here's a good article on using triggers for auditing.

http://weblogs.asp.net/jgalloway/archive/2008/01/27/adding-simple-trigger-based-auditing-to-your-sql-server-database.aspx

Edit:

If you want to stop all deletes on a table, you can use the following trigger.

CREATE TRIGGER dbo.MyTable_Delete_Instead_Of_Trigger
ON dbo.MyTable
INSTEAD OF DELETE
AS
BEGIN

     raiserror('Deletes are not allowed.', 16, 1)
END

Upvotes: 2

Related Questions