Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deny OR Revoke "Drop object" Permission Expand / Collapse
Author
Message
Posted Tuesday, August 11, 2009 5:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, November 14, 2013 7:42 PM
Points: 326, Visits: 393
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
Post #768507
Posted Tuesday, August 11, 2009 8:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:31 AM
Points: 10,910, Visits: 12,548
I'm not sure you can have truncate permissions without having DROP.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #768667
Posted Tuesday, August 11, 2009 9:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 07, 2014 4:50 PM
Points: 2,897, Visits: 5,966
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.


To help us help you read this

For better help with performance problems please read this
Post #768716
Posted Tuesday, August 11, 2009 11:34 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, November 14, 2013 7:42 PM
Points: 326, Visits: 393
i have this thread which can solve the problem
http://www.sqlservercentral.com/Forums/Topic581296-359-2.aspx

thanks and regards
ramu
Post #769117
Posted Friday, August 21, 2009 6:25 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, April 03, 2014 10:06 PM
Points: 6,621, Visits: 1,851
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #775490
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse