Microsoft KB Archive/179151

= BUG: Application Error When Expanding a Stored Procedure =

Article ID: 179151

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q179151



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
 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). Execute the SQL query above to find the orphaned stored procedure.  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  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: kbbug kbsqlserv650bug KB179151

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.