Microsoft KB Archive/186975

= BUG: sp_renamedb Does Not Update dbname Column in Syslogins =

Article ID: 186975

Article Last Modified on 2/14/2005

-

APPLIES TO


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

-



This article was previously published under Q186975



BUG #: 18026 (SQLBUG_65)



SYMPTOMS
After using the sp_renamedb system stored procedure to rename a database, all users whose default database was the renamed database will receive the following errors when logging in to SQL Server:

Msg No: 911 Severity: 16 State: 2

Attempt to locate entry in Sysdatabases for database  by

name failed - no entry found under that name. Make sure that name is

entered properly.

Msg No: 4001 Severity: 11 State: 1

Cannot open default database 

Both errors may occur twice.

After dismissing the errors, the user will be logged in and the current database will be master, even if the user is not permitted in master.



CAUSE
The dbname column in the syslogins table stores the name of the users' default database. The sp_renamedb stored procedure is not updating this column to the new database name.



WORKAROUND
To work around this problem, the system administrator (sa) should run the statements given below. These statements will update the dbname column of syslogins to reflect the new database name.

sp_configure 'allow updates', 1 go  reconfigure with override go

update syslogins set dbname = 'newdbname' where dbname = 'olddbname'

sp_configure 'allow updates', 0 go  reconfigure with override go

NOTE: Substitute your new database and old database names for 'newdbname' and 'olddbname', respectively.



STATUS
Microsoft has confirmed this to be a problem in 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.

Additional query words: st proc

Keywords: kbbug kbpending KB186975

-

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

© Microsoft Corporation. All rights reserved.