StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You cannot assign DROP or CREATE permissions on a single table, as those are schema and database level permissions: </p> <ul> <li><p><a href="http://msdn.microsoft.com/en-US/library/ms173790(v=SQL.90).aspx" rel="noreferrer">DROP TABLE</a> 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.</p></li> <li><p><a href="http://msdn.microsoft.com/en-US/library/ms174979(v=SQL.90).aspx" rel="noreferrer">CREATE TABLE</a> requires CREATE TABLE permission in the <a href="http://msdn.microsoft.com/en-us/library/ms178569.aspx" rel="noreferrer">database</a> and ALTER permission on the <a href="http://msdn.microsoft.com/en-us/library/ms187940.aspx" rel="noreferrer">schema</a> in which the table is being created.</p></li> </ul> <p>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:</p> <ol> <li><p>If you simply need to change the structure of the table, you should use the <a href="http://msdn.microsoft.com/en-us/library/ms177570(SQL.90).aspx" rel="noreferrer">TRUNCATE TABLE</a> statement to delete all the records (without logging) and then use the <a href="http://msdn.microsoft.com/en-us/library/ms190273.aspx" rel="noreferrer">ALTER TABLE</a> statement to add/remove columns.</p></li> <li><p>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 <a href="http://www.functionx.com/sqlserver/Lesson03.htm" rel="noreferrer">using schemas in MS SQL</a> 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. </p></li> </ol>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload