Microsoft KB Archive/247235

= FIX: Sp_Change_Users_Login with Auto_Fix Fails When Run with Local Cursor Database Option Set =

Article ID: 247235

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q247235



BUG #: 56868 (SQLBUG_70)



SYMPTOMS
When the database option default to local cursor is set to TRUE, the system stored procedure sp_change_users_login fails with the following errors if you are using the Auto_Fix option:

Server: Msg 16916, Level 16, State 1, Procedure sp_change_users_login, Line 148

A cursor with the name 'Cursor110_Users' does not exist.

Server: Msg 16916, Level 16, State 1, Procedure sp_change_users_login, Line 152

A cursor with the name 'Cursor110_Users' does not exist.

Server: Msg 16916, Level 16, State 1, Procedure sp_change_users_login, Line 155

A cursor with the name 'Cursor110_Users' does not exist.



WORKAROUND
Set the database option default to local cursor to FALSE, if you need to run this stored procedure. You can do this by running the following command: sp_dboption '', 'default to local cursor', False



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.



MORE INFORMATION
The Auto_Fix option links user entries in the sysusers table in the current database to logins of the same name in syslogins. It is recommended that the result from the Auto_Fix statement be checked to confirm that the links made are correct. Avoid using Auto_Fix in security-sensitive situations. Auto_Fix makes best estimates on links, possibly allowing a user more access permissions than intended.

For more information on the syntax and usage of the stored procedure sp_change_users_login, refer to the SQL Server Books Online topic sp_change_users_login (TSQL).