What permission required to DROP TABLE ?

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,

Answer 1

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

Answer 2

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

 

Answer 3

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.

Answer 4

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