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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy