PRB: Upgrading SQL Server 6.5 Databases with Integrated Logins to SQL Server 7.0 or SQL Server 2000 (291366)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 7.0
This article was previously published under Q291366

SYMPTOMS

After you upgrade SQL Server 6.5 databases to SQL Server 7.0 or SQL Server 2000 by using the Upgrade Wizard, the Microsoft Windows NT logins in SQL Server 6.5 may be changed to Standard SQL logins in the upgraded version of SQL Server.

CAUSE

The computer that is running SQL Server 6.5 may not have had the correct Windows logins working before the upgrade was performed. For instance, this may occur if the SQL Server 6.5 data files were moved from a production server to a test server and the original Microsoft Windows NT users or groups do not exist on the test server, or they have not been granted SQL Server logins. In SQL Server 6.5 you use the xp_grantlogin extended stored procedure or Security Manager to grant logins for Windows NT users and groups. In SQL Server 6.5, SQL Server uses the following registry key to check if a Windows user or group has login access to SQL Server:

HKLM\Software\Microsoft\MSSQLServer\MSSQLServer

During the upgrade process, the upgrade program opens the registry key on the computer that is running SQL Server 6.5, and reads the security identification numbers (SIDs) of all the accounts that have integrated logins granted to them. For each one of those Windows users or groups the setup program runs the sp_grantlogin stored procedure to provide access. If the accounts that use integrated logins do not have the correct registry key permissions, the upgrade process changes "Windows logins" to "Standard logins."

WORKAROUND

Before you perform an upgrade, Microsoft recommends that you check to see if SQL Server 6.5 Windows NT integrated logins work properly. On a computer that is running SQL Server 6.5, you can use the syslogins system table to check the SQL standard logins information, and use the xp_logininfo extended procedure to check the Windows NT User/Groups login information. If Windows NT integrated logins work properly on the computer that is running SQL Server 6.5 before the upgrade, the upgrade process should migrate the logins correctly.

If you move SQL Server 6.5 data files to a new computer and you then try to upgrade to SQL Server 7.0 or SQL Server 2000 on the new computer, you should use the following general procedure for the database upgrade:
  1. If there are local groups or users who need access to SQL Server, then you must create the users and groups on the new server.
  2. You must grant access to all the Windows users and groups by executing the xp_grantlogin extended stored procedure.

MORE INFORMATION

SQL Server 7.0 and SQL Server 2000 no longer rely on using the registry key for securing access to the server. Instead, the granting of access to the server is based on the Windows user or group SIDs. The Windows user name or SIDs are stored in the SQL Server system tables.
Modification Type:MinorLast Reviewed:11/16/2004
Keywords:kbprb KB291366
©2004 Microsoft Corporation. All rights reserved.