Dropping a table in a SQL Server Stored Procedure

To delete a permanent table (not a temp table) in a sql server stored procedure, use this code:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TableName]') AND type in (N'U'))

DROP TABLE [TableName]

However, we have run into situations where the drop table doesn't work, and you get an error message. It turned out to be a security permissions issue. Yet all other commands in the stored procedure worked. But the drop table command does not.

The workaround was to run the drop table command under a different user security account:

EXECUTE AS User='DOMAIN\UserID';

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Tablename]') AND type in (N'U'))

DROP TABLE [TableName]

REVERT;