DROP permission of tables in SQL Server databases

Question

When executing the (T-SQL) DROP command below in SQL server the following message is displayed:

Cannot drop the table '[dbo]. [TABLE]', because it does not exist or you do not have permission.

Commando

EXEC sp_MSForEachTable 'DROP TABLE [?]'

However when executing another T-SQL command or a basic DROP Table the above error message is not displayed and the command is executed correctly.

DROP TABLE TABELA
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Both of the above commands work, for what reason EXEC sp_MSForEachTable 'DROP TABLE [?]' need a permission? and how to give this permission?

Answer 1

Good the command EXEC sp_MSForEachTable It’s an undocumented Sqlserver trial. It is very good when you come across an activity, where you need to execute a command on all tables. This precedent loops activities through the tables.

There is the variation of this command for the database in which the sp_MSforeachdb.

The level of permission that it first obviously requires access to the database and permission to create stored procedures, by having the possibility to manipulate several tables, without necessarily having knowledge of them. If a command was executed to change or remove a tuple from a table and this command was executed all tables that had a tuple with the same name would be affected.

This article explains very well its operation.