Microsoft KB Archive/115245

{|
 * width="100%"|

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, database ,

owner dbo

Msg 229, Level 14, State 1

DELETE permission denied on object, database ,

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'

Login as 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