August 11, 2009 at 5:47 am
Hi all,
I want deny or revoke users from dropping objects in SQL Server 2008/2005
where as they allow with truncate and delete rows permission.
Please help guys.
Regards
Ramu V
August 11, 2009 at 8:39 am
I'm not sure you can have truncate permissions without having DROP.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 11, 2009 at 9:25 am
One of the things that I've seen DDL triggers used for is a sanity check on dropped tables... It's one the more popular examples of DDL triggers I've seen in the MS documentation...
CREATE TRIGGER no_drop
ON DATABASE
FOR DROP_TABLE
AS
PRINT 'Tables cannot be dropped!'
ROLLBACK;
The above will keep everyone from dropping tables. You'd need to modify it to allow only certain users to drop the table, and/or just disable it when you need to drop something and then re-enable it when you are done...
-Luke.
August 11, 2009 at 11:34 pm
i have this thread which can solve the problem
http://www.sqlservercentral.com/Forums/Topic581296-359-2.aspx
thanks and regards
ramu
August 21, 2009 at 6:25 pm
You also have to worry about ALTER TABLE. Here I explain the issue and why the DDL trigger works:
Granting limited permissions to create views in another schema in SQL Server - Part 2
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply