BUG: sp_renamedb Does Not Update dbname Column in Syslogins

ID: Q186975

The information in this article applies to:
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 <old_db_name> 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 <old_db_name>

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          : SSrvStProc kbbug6.50 kbbug6.00 
Version           : WINNT:6.0,6.5
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: April 16, 1999