BUG: Stored Procedure Holds EX_lock on a Table

  - Microsoft SQL Server version 4.2

SYMPTOMS

Executing a stored procedure that depends on a table which has been dropped and recreated after the creation of the SP will cause the following error:

   Msg 229, Level 14, State 1
   DELETE permission denied on object <table name>, database <db_name>,
   owner dbo
   Msg 229, Level 14, State 1
   DELETE permission denied on object <table name>, database <db_name>,
   owner dbo

For example:

Login as sa (dbo of pubs)

   - Create a sample table in the pubs db:
        create table test (c int)
   - Create a sample sp in the master db:
        create proc u_sp_test as delete from pubs..test
   - Grant execute on u_sp_test to public
   - Drop and recreate the table test
   - Create a login id 'testuser'

   - Execute 'u_sp_test'

>From another process execute sp_lock

spid     locktype               table_id        page      dbname
------   --------------------   -----------     -----     -------------
3        Sh_intent              496004798       0         master
3        Ex_extent              0               128       tempdb
4        Ex_table               688005482       0         pubs

WORKAROUND

Drop and recreate the stored procedure in question.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 4.2. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.