locked
What permission required to DROP TABLE ? RRS feed

  • Question

  • Hi,
    I would execute the following query to allow a user to create table

    USE dbname
    GRANT CREATE TABLE TO username

    but it doesnt let me execute the following query
    GRANT DROP TABLE TO username

    I am wondering if there is a way to do this.

    I know db_ddladmin does that. But I dont want to assign the whole role or schema to do that. I want specific privilege

    According to SQL Server documentation, we need CONTROL permission to do this so I tried following query

    GRANT CONTROL TO username

    and

    GRANT  CONTROL ON tablename TO username

    neither of them helped..

    Please tell me what permission do I require to DROP a table.

    Thanks,


    Wednesday, May 2, 2007 2:23 PM

Answers

  • DROP is not a grantable permission.

     

    People who can drop a table are:

     

    The dbo

    The owner of the schema

    The owner of the table (usually the schema owner but it can be changed to someone else)

    members of the db_ddladmin fixed database role

    members of the db_owner fixed database role

    members of the sysadmin fixed server role

    grantees of the CONTROL permission on the table or permissions that imply control on the table

    grantees of the ALTER permission on the schema or permissions that imply alter on the schema.

     

    hth,

     

    -Steven Gott

    S/DET

    SQL Server

     

    Wednesday, May 2, 2007 3:49 PM

All replies

  • Hi,

     

    DROP TABLE needs ALTER permission on the schema to which the table belongs, CONTROL permission on the table, or 

    membership in the db_ddladmin fixed database role.

     

    USE dbname

    GRANT ALTER ON OBJECT :: tablename TO username;

    GO;

     

    CU

    tosc


    Wednesday, May 2, 2007 3:31 PM
  • DROP is not a grantable permission.

     

    People who can drop a table are:

     

    The dbo

    The owner of the schema

    The owner of the table (usually the schema owner but it can be changed to someone else)

    members of the db_ddladmin fixed database role

    members of the db_owner fixed database role

    members of the sysadmin fixed server role

    grantees of the CONTROL permission on the table or permissions that imply control on the table

    grantees of the ALTER permission on the schema or permissions that imply alter on the schema.

     

    hth,

     

    -Steven Gott

    S/DET

    SQL Server

     

    Wednesday, May 2, 2007 3:49 PM
  •  tosc wrote:

    Hi,

    DROP TABLE needs ALTER permission on the schema to which the table belongs, CONTROL permission on the table, or

    membership in the db_ddladmin fixed database role.

    USE dbname

    GRANT ALTER ON OBJECT :: tablename TO username;

    GO;

    CU

    tosc



    I cannot GRANT on objects, because there are plenty of.. Is there any way to grant on all objects of the database ?


     Steven Gott - MS wrote:

    grantees of the CONTROL permission on the table or permissions that imply control on the table





    CONTROL on a table never works. I tried that. Its in my last post.

    Wednesday, May 2, 2007 4:00 PM
  • this will never work:

     

    "GRANT DROP TABLE TO username"

     

    because DROP is not grantable.

     

     This will work:

     

    USE dbname
    GRANT CREATE TABLE TO username

     

    EXECUTE AS USER = 'username'

    go

     

    CREATE TABLE T1 (c1 int)

    go

     

    REVERT

    go


    GRANT  CONTROL ON tablename TO username

    EXECUTE AS USER = 'username'

    go

     

    DROP TABLE T1

    go

     

    REVERT

    go

     

    hth,

     

    -Steven Gott

    S/DET

    SQL Server

    Wednesday, May 2, 2007 4:15 PM