t-SQL grant permission for table drop and create

Question

How can I Grant some users permission to drop and create a single table only in the SQL 2005 database accessed by our VB.net 2005 Win app?

Some articles suggest Granting Control rights to the table but I cannot make this work. If you think this is teh way to go, can you show me the correct syntax?

Answer 1

You cannot assign DROP or CREATE permissions on a single table, as those are schema and database level permissions:

• DROP TABLE requires ALTER permission on the schema to which the table belongs, CONTROL permission on the table, or membership in the db_ddladmin fixed database role.

• CREATE TABLE requires CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.

If the user has control permissions on the table they may be able to drop it, but you would not be able to create it again. There are two approaches that you could take depending on your requirements:

1. If you simply need to change the structure of the table, you should use the TRUNCATE TABLE statement to delete all the records (without logging) and then use the ALTER TABLE statement to add/remove columns.

2. If you really want the user to be able to drop and then create the table again you will need to create the table in a different schema and assign the user the correct permissions for that schema. See this article about using schemas in MS SQL to get you started. Once you have the table in a separate schema you can grant the db_ddladmin role for the new schema to the user and they should be able to create and delete only tables in that schema.

Answer 2

Use this:

DENY ALTER ON SCHEMA::dbo 

But this doesn't prevent the user from granting back this right to himself.