SQL Table Deletions

[winty03]

Hello, I am new to SQL, but had a few questions. We are experiencing random deletions from our database. We have things secured down and for the few that do have access to the SQL database no one has deleted anything. Couple of questions; is there a way to find out who deleted a table? Does a table get so big that it dumps itself? Is there a way to lock the tables from being deleted, but allow modification and or updates? We have a table being deleted every 2 months or so. We do have backups of the DB but we have no explanation for this happening. Any ideas?

 

[mrdenny]

is there a way to find out who deleted a table?

Yes. You need to purchase a piece of software called Lumigent Log Explorer. It will allow you to go through your log backups (or active log) and see who dropped the object.

Does a table get so big that it dumps itself?

Nope.

Is there a way to lock the tables from being deleted

Nope. (You can prevent a regular user from doing so, however is someone is comming in with database owner or system administrator rights they can not be stopped from doing anything.)

Keep in mind that by default any member of the SQL Servers Administrators group has admin access to the database.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[winty03]

Would say deleting a table from access that is linked to sql also delete the sql table or just break the link?

 

[mrdenny]

That would just break the link.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[MDXer]

In SQL 2005 you can use DDL triggers that can be used to prevent unauthorized deletion of DB objects.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Catadmin]

But I don't think the DDL triggers work if you're SysAdmin or DBOwner (as Denny said).



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

No they do work for any user. I use DDL triggers on my production database and for a DDL command I must issue the following command first.

DISABLE TRIGGER ALL ON DATABASE

HEre is my trigger.

CREATE TRIGGER [RestrictDDL]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS 

DECLARE @errordata XML
SET @errordata = EVENTDATA()
RAISERROR ('You can not perform this action on a production database. Please contact a DBA for change procedures.', 16, -1)
ROLLBACK
EXEC msdb.dbo.sp_send_dbmail     @profile_name = 'DEFAULT',
    @recipients = 'dba@*****.com',     @subject = 'DDL event has occured on MavNet.'
INSERT DDLAudit
		(LoginName, 
		 UserName,
		 PostDateTime,
		 EventType,
		 DDLOp)
VALUES   (SYSTEM_USER, ORIGINAL_LOGIN(), GETDATE(), 
   @errordata.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)'), 
   @errordata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(2500)') ) 
RETURN;

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

DDL triggers work for every user. DDL triggers don't actually block the command (unless the DDL trigger issues a rollback like the example that Paul provided is used).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

Thank you for the information. I haven't played with DDL triggers against SysAdmin or DBOwner accounts yet. But a thought just occurred to me.

Depending on why you're implementing the DDL triggers... I.E., if you're doing it to prevent accidently object deletion or if you're doing it as a SOX/Security thing...

Any SysAdmin can remove a DDL trigger if he/she is being malicious or thinks it is getting in the way. So while a DDL trigger may work for a user with SysAdmin access, it will only work as long as said SysAdmin chooses not to remove it.

Right?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Cat,
You are correct. Any sysadmin can disable or remove a DDL trigger. I am currently using DDL triggers to prevent the accidental deletion of objects. I only want DBA's running DDL statements in production. And where I'm the Sr. DBA I say who can be a sysadmin so these triggers are working great for me. I work for a privatly owned insurance company and we are not bound by SOX yet, (although I was at my last company)

- Paul
- If at first you don't succeed, find out if the loser gets anything.