Deny or revoke dropping any object for particular user

Question

Deny or revoke dropping any object for particular user

ramuvanparti

SSCrazy

Points: 2368
More actions
August 10, 2009 at 7:48 am

#213264

Hi all,
I want deny or revoke users from dropping object in SQL Server 2008/2005
with create and alter truncate and delete rows permission are there
Please help guys.
Regards
Ramu V

Viewing 7 posts - 1 through 6 (of 6 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

You could try a DDL trigger, but I would be VERY careful because you could get into a situation where YOU can't delete the trigger and then the server is trashed..

but I would recommend a little research into DDL triggers..

CEWII

ramuvanparti SSCrazy Points: 2368 More actions · Answer 2

hi,

Even i thought of using DDl trigger but it will effective to all users.

I dont want to restrict users from changing table view or procedure schema but to restrict them from drop the object in the database. So as we do deny or revoke command to remove particular right i mean create table etc. in the same way to remove drop table permission on the database.

Regards

Ramu V

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

http://www.sqlmag.com/Articles/ArticleID/44064/44064.html?Ad=1

Listing 1: Safety Trigger That Rejects Attempts to Drop or Alter Tables

CREATE TRIGGER trg_protect_sensitivetables ON DATABASE FOR DROP_TABLE

AS

RAISERROR('This database contains sensitive tables.

A trigger protects the tables from being dropped.

If you are sure the DROP operation is safe, disable the trigger first:

DISABLE TRIGGER trg_protect_sensitivetables ON DATABASE;

;

ENABLE TRIGGER trg_protect_sensitivetables ON DATABASE;

.', 10, 1);

ROLLBACK;

GO

MJ

ramuvanparti SSCrazy Points: 2368 More actions · Answer 4

hi all,

thanks for the reply,

i have forum and got good answer that first assign only db_datawriter rights to the user and

explictly assign select delete insert alter and create permission to the user. by this we can restrict the user from droppping the objects where as he/she can select insert delete truncate alter create objects.

thanks

regards

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 5

One small question... why do we have 10, 1); in following code. Is this mandatory to have 10, 1); or 20, 3); or anything will also work fine?

ENABLE TRIGGER trg_protect_sensitivetables ON DATABASE;

.', 10, 1);

-LK

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 6

10,1 is severity and state parameters of raiseerror.

http://msdn.microsoft.com/en-us/library/ms178592.aspx

Manu