What permission required to DROP TABLE ?

SQL Server Security

Fahad349


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,





Top

Re: What permission required to DROP TABLE ?

tosc


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







Top

Re: What permission required to DROP TABLE ?

Steven Gott - MS

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






Top

Re: What permission required to DROP TABLE ?

Fahad349

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.





Top

Re: What permission required to DROP TABLE ?

Steven Gott - MS

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





Top