Answered by:
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,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 usernameEXECUTE AS USER = 'username'
go
CREATE TABLE T1 (c1 int)
go
REVERT
go
GRANT CONTROL ON tablename TO usernameEXECUTE AS USER = 'username'
go
DROP TABLE T1
go
REVERT
go
hth,
-Steven Gott
S/DET
SQL Server
Wednesday, May 2, 2007 4:15 PM