How to create SQL Server 2008 Policy that prevent a table to be deleted

Question

How to create SQL Server 2008 Policy that prevent a table to be deleted

Ed7

SSCrazy Eights

Points: 8519
More actions
June 23, 2010 at 9:59 am

#141679

Hello room,
How to create SQL Server 2008 Policy that prevent a table to be deleted?
I am not sure when I created a new Condition...which Facet should I select to prevent a table
to be deleted.
Can someone give me hint and help?
Thanks,
Edwin

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you want to prevent one specific table or all tables from being dropped?

i assume you mean some people have the rights/roles to drop the tables, you are not going to change their rights, but you want to prevent the drop anyway?

in that case you'll want to create a database trigger to prevent the drop from happening.

CREATE TRIGGER [TRDB_PreventTableDrop]

on DATABASE

FOR

DROP_TABLE

AS

BEGIN

--prevent and and all drop table commands in this database, even if done by sysadmins.

ROLLBACK

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ed7 SSCrazy Eights Points: 8519 More actions · Answer 2

Thanks for your time and help.

I tested your TRIGGER and it worked on AdventureWorks database.

My scenario is like this:

I created a “Marketing” database.

Then, I created several login and granted one of the user with dbowner privileges.

The user with dbowner should able to create and delete a table.

I wanted to have a notice when he is going to delete a table.

Lowell SSC Guru Points: 323518 More actions · Answer 3

instead of having a rollback command as the body of the trigger, you can send an email instead....so you can get notification that it ALREADY happened and not rollback the command, or you can do both: send an email that says susername() tried to drop a table, and then roll back...

there's a couple of active threads right now on how to send an email that you can use as a model.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!