BUG: Application Error When Expanding a Stored Procedure

 Last reviewed: January 22, 1998
Article ID: Q179151
The information in this article applies to:
  • Microsoft SQL Server, versions 6.0 and 6.5
BUG #: 17701 (6.5) 
       15729 (6.0)

SYMPTOMS

If there is a stored procedure in sysobjects belonging to an invalid database user, you will receive a Dr. Watson application error with Sqlew.exe if you attempt to use SQL Enterprise Manager to expand the Stored Procedure folder under the database.

WORKAROUND

To work around this problem, remove, rename or re-create the stored procedure under a valid user who has CREATE PROCEDURE privileges.

STATUS

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

MORE INFORMATION

Use the following SQL query to find stored procedures that belong to an invalid database user: 

   select name,uid from sysobjects
   where uid NOT IN (select uid from sysusers)
      and type = 'P'

Steps to Remove the Orphaned Stored Procedure

  1. Use either ISQL/W or the SQL Enterprise Manager SQL Query Tool to connect to a database as the database owner (DBO) or system administrator (SA).

  2. Execute the SQL query above to find the orphaned stored procedure.

  3. Execute the following statements: 

          use USER_DB       -- user_db is the database with the orphaned object
      go

      sp_configure allow,1     -- allows update to system tables
      reconfigure with override
      go

      update sysobjects set uid=1
      where name = 'name of stored procedure returned in Step 2'
      go

      drop procedure <name of stored procedure returned in Step 2>
      go

      sp_configure allow,0     -- disallows update to system tables
      reconfigure with override
      go

Additional query words: permission unhandled exception GPF AV access
violation proc st stproc
Keywords : kbbug6.00 kbbug6.50 SSrvEntMan SSrvGPF
Version : WINNT: 6.0 6.5
Platform : winnt
Issue type : kbbug
Solution Type : kbworkaround

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: January 22, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.