Q115245 - BUG: Stored Procedure Holds EX

BUG: Stored Procedure Holds EX_lock on a Table

Q115245

The information in this article applies to:

Microsoft SQL Server version 4.2x

BUG# NT: 789 (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

Also, an exclusive lock will be retained on the table until another SQL command is executed by the process.

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'

Msg 229, Level 14, State 1
DELETE permission denied on object test, database pubs, owner dbo
Msg 229, Level 14, State 1
DELETE permission denied on object test, database pubs, owner dbo

>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.

Additional query words: sysdepends sysprocedures Windows NT

Keywords : kbprogramming
Issue type :
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ420OS2