Microsoft KB Archive/282412

= BUG: Generate SQL Scripts Operation in Enterprise Manager Doesn't Show Correct Owner =

Article ID: 282412

Article Last Modified on 9/4/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q282412



BUG #: 100961 (SQLBUG_70)



SYMPTOMS
When a stored procedure that is not owned by the database owner (DBO) has the same name as a stored procedure that is owned by DBO, and they both exist in the same database, the Generate SQL Scripts operation always shows the script for the DBO-owned stored procedure regardless of which stored procedure you choose.

Note that this is only a scripting error; the two stored procedures actually exist distinctly in the system catalog.

This problem is also seen when scripting tables, views, and rules.



WORKAROUND
You can implement either of the following workarounds depending on your requirements:   If the problem is seen with a stored procedure, the contents of the stored procedure can be returned by executing the following statement in Query Analyzer: sp_helptext 'owner.spname'  One of the two objects (either DBO or non-DBO) can be renamed so that two distinct names exist and each can be individually scripted. Note that after you change the name, all other objects, jobs, and so on that reference the modified object must be altered to reflect the change.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.



Steps to Reproduce the Behavior
 In Enterprise Manager, create a login named myLogin and provide Public role privileges to this login for the Pubs database. Default the login to the Pubs database.  Log in to SQL Server using the myLogin login and create a stored procedure using Query Analyzer; for example: USE pubs go CREATE PROCEDURE myProc as SELECT * FROM authors go </li>  Log in to SQL Server using the sa login and create a stored procedure using Query Analyzer: USE pubs go CREATE PROCEDURE myProc as SELECT * FROM titles go </li> In Enterprise Manager, select the stored procedure myProc created with the login myLogin. Right-click the procedure, select All Tasks and then select Generate SQL Scripts. In Objects to be Scripted on the General tab, the stored procedure owner name is shown as the DBO. The preview also incorrectly shows the contents of the stored procedure created by the DBO.</li></ol>

You can follow the same steps to recreate the problem for tables and views.

Keywords: kbbug kbpending KB282412

-

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

© Microsoft Corporation. All rights reserved.