T-SQL permission to delete a table and create

Question

How can I grant some users permission to delete and create a separate table only in the SQL 2005 database accessible by our VB.net 2005 Win application?

Some articles suggest granting management rights to a table, but I cannot do this work. If you think this is the way, can you show me the correct syntax?

Answer 1

You cannot assign DROP or CREATE permissions to a single table, as these are permissions at the schema and database level:

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

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

If the user has control permissions in the table, they can drop it, but you cannot create it again. Depending on your requirements, you can choose two approaches:

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

• If you really want the user to be able to refuse, and then create the table again, you will need to create the table in a different scheme and assign the user the correct permissions for this scheme. See this article on using schemas in MS SQL to get you started. When you have a table in a separate schema, you can provide the db_ddladmin role for the new schema to the user, and they should be able to create and delete only tables in this schema.

Answer 2

Use this:

DENY ALTER ON SCHEMA::dbo 

But this does not prevent the user from granting this right to himself.