SQL Server‎ > ‎

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;
 
 
 
 
Comments