SQL Server 2005 , How to Deny Drop Table to db_dlladmin Role

Posted on 2009-05-02
Last Modified: 2012-05-06
I have a a database user user1  has a database role membership in db_ddladmin
How can Deny him from  Drop objects (tables, views) , in other words he can Create objects but he cant drop it?
Question by:ali_alannah
    LVL 3

    Expert Comment

    I think an easier solution may be to create a new database role and only grant the create table and create view permissions...I will test that quick and reply back shortly.
    LVL 3

    Assisted Solution

    Sorry, did not work the way I thought it would. I created a new Role using this syntax;


    Then I granted the Create Table permission to the Role;

    grant create table to [MyRole]

    Then I added MyUser to the Role;

    EXEC sp_addrolemember N'MyRole', N'MyUser'

    However when I connected as the MyUser account I was able to both create a table and drop the table. I guess the create table permission must also allow the drop permission by default, bummer.
    LVL 142

    Accepted Solution

    I agree: ddl_admin can both create and drop any tables.
    a login/user that owns a table (or any other object), can also drop it.

    note: a table, where a foreign key points to cannot be dropped unless the foreign key is dropped first!

    note 2: as from sql 2005, you can create database/server triggers to "catch" drop table (among others) statements, and issue a ROLLBACK, so the drop cannot be performed.

    I presume that is the only way you can solve that issue.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now