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: Tuesday, September 9, 2014 3:47 AM
Points: 326, Visits: 394
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: Today @ 12:13 PM
Points: 10,342, Visits: 13,351
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: Today @ 9:29 AM
Points: 2,655, Visits: 6,016
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: Tuesday, September 9, 2014 3:47 AM
Points: 326, Visits: 394
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: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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