How to move a DHCP database from a computer that is running Windows NT Server 4.0, Windows 2000, or Windows Server 2003 to a computer that is running Windows Server 2003
View products that this article applies to.
Article ID : 325473
Last Review : March 27, 2007
Revision : 19.5
This article was previously published under Q325473
On This Page
SUMMARY
Export the DHCP database from a server that is running Windows NT Server 4.0 or Windows 2000
Export the DHCP database from a server that is running Microsoft Windows Server 2003
Install the DHCP server service on the server that is running Windows Server 2003
Import the DHCP database
Authorize the DHCP server
REFERENCES
SUMMARY
This step-by-step article describes how to move a Dynamic Host Configuration Protocol (DHCP) database from a computer that is running Microsoft Windows NT Server 4.0, Microsoft Windows 2000, or Microsoft Windows Server 2003 to a computer that is running Windows Server 2003.
Note You can use the Microsoft Windows backup utility (ntbackup.exe) to back up and restore the DHCP database on a single server. Do not use the backup utility to migrate or to move a DHCP database from one DHCP server to another.
Back to the top
Export the DHCP database from a server that is running Windows NT Server 4.0 or Windows 2000
1. Stop the DHCP Server service on the server: a. Log on to the source DHCP server by using an account that is a member of the local Administrators group.
b. Click Start, click Run, type cmd in the Open box, and then click OK.
c. At the command prompt, type net stop dhcpserver, and then press ENTER. You receive a "The Microsoft DHCP Server service is stopping. The Microsoft DHCP Server service was stopped successfully" message.
d. Type exit, and then press ENTER.
2. Compact the DHCP database by using the JetPack utility: a. Click Start, click Run, type cmd in the Open box, and then click OK.
b. At the command prompt, type cd %systemroot%\system32\dhcp, and then press ENTER.
c. Type jetpack dhcp.mdb temp.mdb, and then press ENTER.
d. After the database is compacted successfully, type exit, and then press ENTER.
3. Export the DHCP database by using the DHCP Export Import utility (Dhcpexim.exe). You can obtain this utility from the Windows 2000 Resource Kit Supplement 1. You can also visit the following Microsoft Web site to obtain Dhcpexim.exe:
http://support.microsoft.com/kb/927229 (http://support.microsoft.com/kb/927229)
To export the database: a. Install the Dhcpexim.exe utility, and then start the Dhcpexim.exe utility.
b. At the Welcome to DHCP Export Import tool screen, click Export configuration of the local service to a file, and then click Ok.
c. In the File name box, type the file name for the exported file, and then click Save. For example, type dhcpdatabase.txt.
d. Click the scope or scopes that you want to export, click to select the Disable the selected scopes on local machine before export check box, and then click Export.
e. Click OK.
4. Disable the DHCP Server service on the server. Disabling the DHCP Server service prevents the service from starting after the database has been transferred. To disable the DHCP Server service: a. Click Start, point to Settings, click Control Panel, and then double-click Services.
b. In the Service list, click Microsoft DHCP Server, click Startup, click Disabled, and then click OK.
c. If the service is started, click Stop, and then click Yes to confirm the stopping of the service.
d. Click Close to close the Services dialog box.
Important Dhcpexim.exe is required to move the database successfully from a server that is running Windows 2000 or Windows NT 4.0 to a server that is running Windows Server 2003. Netsh commands for DHCP are not available in Windows NT 4.0.
Note If only the configuration (not the database) is required, use the following command (instead of Dhcpexim.exe) on the Windows 2000-based server that you want to export from. (Do not use Dhcpexim.exe.)
netsh dhcp dump >C:\dhcp.txt
where C:\dhcp.txt is the name and path of the export file that you want to use.
Note The export option does not exist in the netsh command on Windows 2000 Server. The netsh dhcp server dump and netsh dhcp server import commands are not compatible. If you try to import the data that is created by netsh dhcp server dump > C:\dhcp.txt by using netsh DHCP server import > C:\dhcp.txt, you receive the following error message on the Windows Server 2003-based computer:
The request is not supported.
You can migrate the exported configuration file to the new Windows Server 2003 server by using the following command:
netsh exec c:\dhcp.txt
Dhcpexim.exe is not supported in Windows Server 2003. If a database is exported on a Windows 2000-based computer by using Dhcpexim.exe, and you try to import the data to Windows Server 2003, Dhcpexim.exe quits, and you receive the following error message:
An error occurred. An attempt was made to load a program with a incorrect format.
If this behavior occurs, export data from Windows 2000 by using dhcpexim and then import the data on the Windows Server 2003 environment by using netsh DHCP server import xyz.txt.
Back to the top
Export the DHCP database from a server that is running Microsoft Windows Server 2003
To move a DHCP database and configuration from a server that is running Windows Server 2003 to another server that is running Windows Server 2003: 1. Log on to the source DHCP server by using an account that is a member of the local Administrators group.
2. Click Start, click Run, type cmd in the Open box, and then click OK.
3. Type netsh dhcp server export C:\dhcp.txt all, and then press ENTER.
Note You must have local administrator permissions to export the data.
Back to the top
Install the DHCP server service on the server that is running Windows Server 2003
To install the DHCP Server service on an existing Windows Server 2003-based computer: 1. Click Start, click Control Panel, and then double-click Add or Remove Programs.
2. Click Add/Remove Windows Components.
3. In the Windows Component Wizard, click Networking Services in the Components box, and then click Details.
4. Click to select the Dynamic Host Configuration Protocol (DHCP) check box if it is not already selected, and then click OK.
5. In the Windows Components Wizard, click Next to install the selected components. Insert the Windows Server 2003 CD into your computer CD drive or DVD drive if you are prompted to do this. Setup copies the DHCP server and tool files to your computer.
6. When Setup is complete, click Finish.
Back to the top
Import the DHCP database
Note You may receive an "access denied" message during this procedure if you are not a member of the Backup Operators group. If you receive an "Unable to determine the DHCP server version for server" error message, make sure that the DHCP Server service is running on the server and that the user logged on is a member of the local Administrators group.
Important Do not use Dhcpexim.exe to import a DHCP database in Windows Server 2003. Additionally, if the target Windows 2003 server is a member server, and if you plan to promote it to a domain controller, we suggested that you perform the DHCP database migration before promoting it to a domain controller. Although you can migrate the DHCP database to a Windows 2003 domain controller, the migration to a member server will be easier because of the existence of the local administrator account.1. Log on as a user who is an explicit member of the local Administrators group. A user account in a group that is a member of the local Administrators group will not work. If a local Administrators account does not exist for the domain controller, restart the computer in Directory Services Restore Mode, and use the administrator account to import the database as described later in this section.
2. Copy the exported DHCP database file to the local hard disk of the Windows Server 2003-based computer.
3. Verify that the DHCP service is started on the Windows Server 2003-based computer.
4. Click Start, click Run, type cmd in the Open box, and then click OK.
5. At the command prompt, type netsh dhcp server import c:\dhcpdatabase.txt all, and then press ENTER, where c:\dhcpdatabase.txt is the full path and file name of the database file that you copied to the server.
Note When you try to export a DHCP database from a Windows 2000 domain controller to a Windows Server 2003 member server of the domain, you may receive the following error message:
Error initializing and reading the service configuration - Access Denied
Note You must have local administrator permissions to import the data.
6. To resolve this issue, add the Windows Server 2003 DHCP server computer to the DHCP Admins group at the Enterprise level.
7. If the "access is denied" error message occurs after you add the Windows Server 2003 DCHP server computer to the DHCP Admins group at the Enterprise level that is mentioned in step 4, verify that the user account that is currently used to import belongs to the local Administrators group. If the account does not belong to this group, add the account to that group, or log on as a local administrator to complete the import.
Note If the DHCP IMPORT or EXPORT command fails for users who are not explicit members of the local Administrators group, you must apply the following hotfix on the Windows Server 2003-based computer:
833167 (http://support.microsoft.com/kb/833167/) A Volume Shadow Copy Service (VSS) update package is available for Windows Server 2003
8. After you receive the message that the command completed successfully, quit the command prompt.
Back to the top
Authorize the DHCP server
1. Click Start, point to All Programs, point to Administrative Tools, and then click DHCP.
Note You must be logged on to the server by using an account that is a member of the Administrators group. In an Active Directory domain, you must be logged on to the server by using an account that is a member of the Enterprise Administrators group.
2. In the console tree of the DHCP snap-in, expand the new DHCP server. If there is a red arrow in the lower-right corner of the server object, the server has not yet been authorized.
3. Right-click the server object, and then click Authorize.
4. After several moments, right-click the server again, and then click Refresh. A green arrow indicates that the DHCP server is authorized.
Back to the top
REFERENCES
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
323416 (http://support.microsoft.com/kb/323416/) How to install and configure a DHCP server in a workgroup in Windows Server 2003
130642 (http://support.microsoft.com/kb/130642/) How to move a DHCP database from one server to another in Windows NT 4.0 and in Windows 2000
890480 (http://support.microsoft.com/kb/890480/) "Access denied" error message when you use the "netsh dhcp server import" command to import a DHCP database from a Windows NT Server 4.0-based computer to a Windows Server 2003-based computer
Back to the top
--------------------------------------------------------------------------------
APPLIES TO
• Microsoft Windows Server 2003, Datacenter Edition (32-bit x86)
• Microsoft Windows Server 2003, Enterprise Edition (32-bit x86)
• Microsoft Windows Server 2003, Standard Edition (32-bit x86)
• Microsoft Windows Server 2003, Datacenter Edition for Itanium-Based Systems
• Microsoft Windows Server 2003, Enterprise Edition for Itanium-based Systems
• Microsoft Windows NT Server 4.0 Standard Edition
• Microsoft Windows 2000 Server
Back to the top
Keywords: kbnetwork kbhowtomaster KB325473
Back to the top
Thursday, January 10, 2008
Moving Security Accounts between MS SQL server
How to transfer logins and passwords between instances of SQL Server
View products that this article applies to.
Article ID : 246133
Last Review : November 2, 2007
Revision : 8.1
This article was previously published under Q246133
On This Page
SUMMARY
How to transfer logins and passwords between servers that are running SQL Server 7.0
How to transfer logins and passwords from SQL Server 7.0 to SQL Server 2000 or between servers that are running SQL Server 2000
How to transfer logins and passwords between instances of SQL Server 2005
A complete resolution to transfer logins and passwords between different versions of SQL Server
Method 1
Method 2
Remarks
SUMMARY
After you move databases to a new server, users may not be able to log in to the new server. Instead, they receive the following error message:
Msg 18456, Level 16, State 1
Login failed for user '%ls'.
You must transfer the logins and passwords to the new server. This article describes how you transfer logins and passwords to a new server.
Back to the top
How to transfer logins and passwords between servers that are running SQL Server 7.0
The SQL Server 7.0 Data Transformation Services (DTS) Object Transfer feature transfers logins and users between two servers, but it does not transfer the passwords for SQL Server authenticated logins. To transfer logins and passwords from a server that is running SQL Server 7.0 to another server that is running SQL Server 7.0, follow the steps in the "A complete resolution to transfer logins and passwords between different versions of SQL Server" section.
Back to the top
How to transfer logins and passwords from SQL Server 7.0 to SQL Server 2000 or between servers that are running SQL Server 2000
To transfer logins and passwords from a SQL Server 7.0 server to an instance of SQL Server 2000, or between two instances of SQL Server 2000, you can use the new DTS Package Transfer Logins Task in SQL Server 2000. To do this, follow these steps: 1. Connect to the SQL Server 2000 destination server, move to the Data Transformation Services in SQL Server Enterprise Manager, expand the folder, right-click Local Packages, and then click New Package.
2. After the DTS package designer opens, click Transfer Logins Task on the Task menu. Complete the information about the Source, Destination and Logins tabs as appropriate.
Important The SQL Server 2000 destination server cannot be running the 64-bit version of SQL Server 2000. DTS components for the 64-bit version of SQL Server 2000 are not available. If you are importing logins from an instance of SQL Server that is on a separate computer, your instance of SQL Server will must be running under a Domain Account to complete the task.
Note The DTS method will transfer the passwords but not the original SID. If a login is not created by using the original SID and user databases are also transferred to a new server, the database users will be orphaned from the login. To transfer the original SID and bypass the orphaned users, follow the steps in the "A complete resolution to transfer logins and passwords between different versions of SQL Server" section.
Back to the top
How to transfer logins and passwords between instances of SQL Server 2005
For more information about how to transfer the logins and passwords between instances of SQL Server 2005, click the following article number to view the article in the Microsoft Knowledge Base:
918992 (http://support.microsoft.com/kb/918992/) How to transfer the logins and the passwords between instances of SQL Server 2005
Back to the top
A complete resolution to transfer logins and passwords between different versions of SQL Server
To do this, use one of the following methods.
Notes• The scripts in the following methods create two stored procedures that are named the sp_hexadecimal stored procedure and the sp_help_revlogin stored procedure in your master database.
• The scripts are dependent on SQL Server system tables. The structure of these tables may change between versions of SQL Server. Selecting directly from system tables is discouraged.
• Review the remarks at the end of this article for important information about the steps in the methods.
• Method 2 assigns logins to roles.
Back to the top
Method 1
This method applies to the following scenarios:• You transfer logins and passwords from SQL Server 7.0 to SQL Server 7.0.
• You transfer logins and passwords from SQL Server 7.0 to SQL Server 2000.
• You transfer logins and passwords between servers that are running SQL Server 2000.
To transfer logins and passwords between different versions of SQL Server, follow these steps:1. Run the following script on the source SQL Server. Continue to step 2 when you finish creating the sp_help_revlogin stored procedure. ----- Begin Script, Create sp_help_revlogin procedure -----
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----
2. After you create the sp_help_revlogin stored procedure, run the sp_help_revlogin procedure from Query Analyzer on the source server. The sp_help_revlogin stored procedure can be used on both SQL Server 7.0 and SQL Server 2000. The output of the sp_help_revlogin stored procedure is login scripts that create logins with the original SID and password. Save the output, and then paste and run it in Query Analyzer on the destination SQL Server. For example:EXEC master..sp_help_revlogin
Back to the top
Method 2
This method applies to the following scenarios:• You transfer logins and passwords from SQL Server 7.0 to SQL Server 2005.
• You transfer logins and passwords from SQL Server 2000 to SQL Server 2005.
• You assign logins to roles.
To transfer logins and passwords between different versions of SQL Server and then assign logins to roles, follow these steps:1. Run the following script on the source SQL Server. USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin_2000_to_2005') IS NOT NULL
DROP PROCEDURE sp_help_revlogin_2000_to_2005
GO
CREATE PROCEDURE sp_help_revlogin_2000_to_2005
@login_name sysname = NULL,
@include_db bit = 0,
@include_role bit = 0
AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @dfltdb varchar (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR STATIC FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** CREATE LOGINS *****/'
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = '' --'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE
BEGIN -- NT login has access
SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')'
PRINT @tmpstr
SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS'
PRINT @tmpstr
END
END
ELSE
BEGIN -- SQL Server authentication
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED'
END
ELSE
BEGIN -- Null password
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD='''''
END
SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
IF @include_db = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET DEFAULT DATABASES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']'
PRINT @tmpstr
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
IF @include_role = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET SERVER ROLES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF @xstatus &16 = 16 -- sysadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin'''
PRINT @tmpstr
END
IF @xstatus &32 = 32 -- securityadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin'''
PRINT @tmpstr
END
IF @xstatus &64 = 64 -- serveradmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin'''
PRINT @tmpstr
END
IF @xstatus &128 = 128 -- setupadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin'''
PRINT @tmpstr
END
IF @xstatus &256 = 256 --processadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin'''
PRINT @tmpstr
END
IF @xstatus &512 = 512 -- diskadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin'''
PRINT @tmpstr
END
IF @xstatus &1024 = 1024 -- dbcreator
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator'''
PRINT @tmpstr
END
IF @xstatus &4096 = 4096 -- bulkadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin'''
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
GO
2. Save the output, and then paste and run the output in SQL Server Management Studio on the destination SQL Server 2005.
Note If the source SQL Server contains a login that has a blank password, the output contains a statement that resembles the following. CREATE LOGIN LoginName WITH PASSWORD = '', CHECK_POLICY = OFF, SID = MySID
Back to the top
Remarks
• Review the output script carefully before you run it on the destination SQL Server. If you have to transfer logins to an instance of SQL Server in a different domain than the source instance of SQL Server, edit the script generated by the sp_help_revlogin procedure, and replace the domain name with the new domain in the sp_grantlogin statements. Because the integrated logins granted access in the new domain will not have the same SID as the logins in the original domain, the database users will be orphaned from these logins. To resolve these orphaned users, see the articles referenced in the following bullet item. If you transfer integrated logins between instances of SQL Servers in the same domain, the same SID is used and the user is not likely to be orphaned.
• After you move the logins, users may not have permissions to access databases that have also been moved. This problem is described as an "orphaned user". If you try to grant the login access to the database, it may fail indicating the user already exists:
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.
For instructions about how to map the logins to the database users to resolve orphaned SQL Server logins and integrated logins, see the following article in the Microsoft Knowledge Base:
240872 (http://support.microsoft.com/kb/240872/) How to resolve permission issues when you move a database between servers that are running SQL Server
For instructions about using the sp_change_users_login stored procedure to correct the orphaned users one-by-one (this will only address users orphaned from standard SQL logins), see the following article in the Microsoft Knowledge Base:
274188 (http://support.microsoft.com/kb/274188/) "Troubleshooting Orphaned Users" topic in Books Online is incomplete
• If the transfer of logins and passwords is part of a move of databases to a new server running SQL Server, see the following article in the Microsoft Knowledge Base for a description of the workflow and steps involved:
314546 (http://support.microsoft.com/kb/314546/) How to move databases between computers that are running SQL Server
• You can do this because of the @encryptopt parameter in the sp_addlogin system stored procedure, that allows a login to be created by using the encrypted password. For more information about this procedure, see the "sp_addlogin (T-SQL)" topic in SQL Server Books Online.
• By default, only members of the sysadminfixed server role can select from the sysxlogins table. Unless a member of the sysadmin role grants the necessary permissions, end users cannot create or run these stored procedures.
• This approach does not try to transfer the default database information for a particular login because the default database may not always exist on the destination server. To define the default database for a login, you can use the sp_defaultdb system stored procedure by passing it the login name and the default database as arguments. For more information about using this procedure, see the "sp_defaultdb" topic in SQL Server Books Online.
• During a transfer of logins between instances of SQL Server, if the sort order of the source server is case-insensitive and the sort order of the destination server is case-sensitive, you must enter all alphabetical characters in passwords as uppercase characters after the transfer of logins to the destination server. If the sort order of the source server is case-sensitive and the sort order of the destination server is case-insensitive, you will not be able to log in with the logins transferred using the procedure outlined in this article, unless the original password contains no alphabetical characters or unless all alphabetical characters in the original password are uppercase characters. If both servers are case-sensitive or both servers are case-insensitive, you will not experience this problem. This is a side effect of the way that SQL Server handles passwords. For more information, see the "Effect on Passwords of Changing Sort Orders" topic in SQL Server 7.0 Books Online.
• When you run the output from the sp_help_revlogin script on the destination server, if the destination server already has a login defined with the same name as one of the logins on the script output, you may see the following error upon execution of the output of the sp_help_revlogin script:
Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 56
The login 'test1' already exists.
Likewise, if a different login exists with the same SID value on this server as the one you are trying to add, you receive the following error message:
Server: Msg 15433, Level 16, State 1, Procedure sp_addlogin, Line 93
Supplied parameter @sid is in use.
Therefore, you must carefully review the output from these commands, examine the contents of the sysxlogins table, and address these errors accordingly.
• The SID value for a particular login is used as the basis for implementing database level access in SQL Server. Therefore, if the same login has two different values for the SID at the database level (in two different databases on that server), the login will only have access to that database whose SID matches the value in syslogins for that login. Such a situation might occur if the two databases in question have been consolidated from two different servers. To resolve this problem, the login in question would have to be manually removed from the database that has a SID mismatch by using the sp_dropuser stored procedure, and then added again by using the sp_adduser stored procedure.
Back to the top
--------------------------------------------------------------------------------
APPLIES TO
• Microsoft SQL Server 7.0 Standard Edition
• Microsoft SQL Server 2000 Personal Edition
• Microsoft SQL Server 2000 Standard Edition
• Microsoft SQL Server 2000 Workgroup Edition
• Microsoft SQL Server 2000 Developer Edition
• Microsoft SQL Server 2000 Enterprise Edition
• Microsoft SQL Server 2005 Standard Edition
• Microsoft SQL Server 2005 Workgroup Edition
• Microsoft SQL Server 2005 Developer Edition
• Microsoft SQL Server 2005 Enterprise Edition
Back to the top
Keywords: kbhowtomaster kbinfo KB246133
Back to the top
View products that this article applies to.
Article ID : 246133
Last Review : November 2, 2007
Revision : 8.1
This article was previously published under Q246133
On This Page
SUMMARY
How to transfer logins and passwords between servers that are running SQL Server 7.0
How to transfer logins and passwords from SQL Server 7.0 to SQL Server 2000 or between servers that are running SQL Server 2000
How to transfer logins and passwords between instances of SQL Server 2005
A complete resolution to transfer logins and passwords between different versions of SQL Server
Method 1
Method 2
Remarks
SUMMARY
After you move databases to a new server, users may not be able to log in to the new server. Instead, they receive the following error message:
Msg 18456, Level 16, State 1
Login failed for user '%ls'.
You must transfer the logins and passwords to the new server. This article describes how you transfer logins and passwords to a new server.
Back to the top
How to transfer logins and passwords between servers that are running SQL Server 7.0
The SQL Server 7.0 Data Transformation Services (DTS) Object Transfer feature transfers logins and users between two servers, but it does not transfer the passwords for SQL Server authenticated logins. To transfer logins and passwords from a server that is running SQL Server 7.0 to another server that is running SQL Server 7.0, follow the steps in the "A complete resolution to transfer logins and passwords between different versions of SQL Server" section.
Back to the top
How to transfer logins and passwords from SQL Server 7.0 to SQL Server 2000 or between servers that are running SQL Server 2000
To transfer logins and passwords from a SQL Server 7.0 server to an instance of SQL Server 2000, or between two instances of SQL Server 2000, you can use the new DTS Package Transfer Logins Task in SQL Server 2000. To do this, follow these steps: 1. Connect to the SQL Server 2000 destination server, move to the Data Transformation Services in SQL Server Enterprise Manager, expand the folder, right-click Local Packages, and then click New Package.
2. After the DTS package designer opens, click Transfer Logins Task on the Task menu. Complete the information about the Source, Destination and Logins tabs as appropriate.
Important The SQL Server 2000 destination server cannot be running the 64-bit version of SQL Server 2000. DTS components for the 64-bit version of SQL Server 2000 are not available. If you are importing logins from an instance of SQL Server that is on a separate computer, your instance of SQL Server will must be running under a Domain Account to complete the task.
Note The DTS method will transfer the passwords but not the original SID. If a login is not created by using the original SID and user databases are also transferred to a new server, the database users will be orphaned from the login. To transfer the original SID and bypass the orphaned users, follow the steps in the "A complete resolution to transfer logins and passwords between different versions of SQL Server" section.
Back to the top
How to transfer logins and passwords between instances of SQL Server 2005
For more information about how to transfer the logins and passwords between instances of SQL Server 2005, click the following article number to view the article in the Microsoft Knowledge Base:
918992 (http://support.microsoft.com/kb/918992/) How to transfer the logins and the passwords between instances of SQL Server 2005
Back to the top
A complete resolution to transfer logins and passwords between different versions of SQL Server
To do this, use one of the following methods.
Notes• The scripts in the following methods create two stored procedures that are named the sp_hexadecimal stored procedure and the sp_help_revlogin stored procedure in your master database.
• The scripts are dependent on SQL Server system tables. The structure of these tables may change between versions of SQL Server. Selecting directly from system tables is discouraged.
• Review the remarks at the end of this article for important information about the steps in the methods.
• Method 2 assigns logins to roles.
Back to the top
Method 1
This method applies to the following scenarios:• You transfer logins and passwords from SQL Server 7.0 to SQL Server 7.0.
• You transfer logins and passwords from SQL Server 7.0 to SQL Server 2000.
• You transfer logins and passwords between servers that are running SQL Server 2000.
To transfer logins and passwords between different versions of SQL Server, follow these steps:1. Run the following script on the source SQL Server. Continue to step 2 when you finish creating the sp_help_revlogin stored procedure. ----- Begin Script, Create sp_help_revlogin procedure -----
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----
2. After you create the sp_help_revlogin stored procedure, run the sp_help_revlogin procedure from Query Analyzer on the source server. The sp_help_revlogin stored procedure can be used on both SQL Server 7.0 and SQL Server 2000. The output of the sp_help_revlogin stored procedure is login scripts that create logins with the original SID and password. Save the output, and then paste and run it in Query Analyzer on the destination SQL Server. For example:EXEC master..sp_help_revlogin
Back to the top
Method 2
This method applies to the following scenarios:• You transfer logins and passwords from SQL Server 7.0 to SQL Server 2005.
• You transfer logins and passwords from SQL Server 2000 to SQL Server 2005.
• You assign logins to roles.
To transfer logins and passwords between different versions of SQL Server and then assign logins to roles, follow these steps:1. Run the following script on the source SQL Server. USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin_2000_to_2005') IS NOT NULL
DROP PROCEDURE sp_help_revlogin_2000_to_2005
GO
CREATE PROCEDURE sp_help_revlogin_2000_to_2005
@login_name sysname = NULL,
@include_db bit = 0,
@include_role bit = 0
AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @dfltdb varchar (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR STATIC FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** CREATE LOGINS *****/'
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = '' --'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE
BEGIN -- NT login has access
SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')'
PRINT @tmpstr
SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS'
PRINT @tmpstr
END
END
ELSE
BEGIN -- SQL Server authentication
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED'
END
ELSE
BEGIN -- Null password
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD='''''
END
SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
IF @include_db = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET DEFAULT DATABASES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']'
PRINT @tmpstr
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
IF @include_role = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET SERVER ROLES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF @xstatus &16 = 16 -- sysadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin'''
PRINT @tmpstr
END
IF @xstatus &32 = 32 -- securityadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin'''
PRINT @tmpstr
END
IF @xstatus &64 = 64 -- serveradmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin'''
PRINT @tmpstr
END
IF @xstatus &128 = 128 -- setupadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin'''
PRINT @tmpstr
END
IF @xstatus &256 = 256 --processadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin'''
PRINT @tmpstr
END
IF @xstatus &512 = 512 -- diskadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin'''
PRINT @tmpstr
END
IF @xstatus &1024 = 1024 -- dbcreator
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator'''
PRINT @tmpstr
END
IF @xstatus &4096 = 4096 -- bulkadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin'''
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
GO
2. Save the output, and then paste and run the output in SQL Server Management Studio on the destination SQL Server 2005.
Note If the source SQL Server contains a login that has a blank password, the output contains a statement that resembles the following. CREATE LOGIN LoginName WITH PASSWORD = '', CHECK_POLICY = OFF, SID = MySID
Back to the top
Remarks
• Review the output script carefully before you run it on the destination SQL Server. If you have to transfer logins to an instance of SQL Server in a different domain than the source instance of SQL Server, edit the script generated by the sp_help_revlogin procedure, and replace the domain name with the new domain in the sp_grantlogin statements. Because the integrated logins granted access in the new domain will not have the same SID as the logins in the original domain, the database users will be orphaned from these logins. To resolve these orphaned users, see the articles referenced in the following bullet item. If you transfer integrated logins between instances of SQL Servers in the same domain, the same SID is used and the user is not likely to be orphaned.
• After you move the logins, users may not have permissions to access databases that have also been moved. This problem is described as an "orphaned user". If you try to grant the login access to the database, it may fail indicating the user already exists:
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.
For instructions about how to map the logins to the database users to resolve orphaned SQL Server logins and integrated logins, see the following article in the Microsoft Knowledge Base:
240872 (http://support.microsoft.com/kb/240872/) How to resolve permission issues when you move a database between servers that are running SQL Server
For instructions about using the sp_change_users_login stored procedure to correct the orphaned users one-by-one (this will only address users orphaned from standard SQL logins), see the following article in the Microsoft Knowledge Base:
274188 (http://support.microsoft.com/kb/274188/) "Troubleshooting Orphaned Users" topic in Books Online is incomplete
• If the transfer of logins and passwords is part of a move of databases to a new server running SQL Server, see the following article in the Microsoft Knowledge Base for a description of the workflow and steps involved:
314546 (http://support.microsoft.com/kb/314546/) How to move databases between computers that are running SQL Server
• You can do this because of the @encryptopt parameter in the sp_addlogin system stored procedure, that allows a login to be created by using the encrypted password. For more information about this procedure, see the "sp_addlogin (T-SQL)" topic in SQL Server Books Online.
• By default, only members of the sysadminfixed server role can select from the sysxlogins table. Unless a member of the sysadmin role grants the necessary permissions, end users cannot create or run these stored procedures.
• This approach does not try to transfer the default database information for a particular login because the default database may not always exist on the destination server. To define the default database for a login, you can use the sp_defaultdb system stored procedure by passing it the login name and the default database as arguments. For more information about using this procedure, see the "sp_defaultdb" topic in SQL Server Books Online.
• During a transfer of logins between instances of SQL Server, if the sort order of the source server is case-insensitive and the sort order of the destination server is case-sensitive, you must enter all alphabetical characters in passwords as uppercase characters after the transfer of logins to the destination server. If the sort order of the source server is case-sensitive and the sort order of the destination server is case-insensitive, you will not be able to log in with the logins transferred using the procedure outlined in this article, unless the original password contains no alphabetical characters or unless all alphabetical characters in the original password are uppercase characters. If both servers are case-sensitive or both servers are case-insensitive, you will not experience this problem. This is a side effect of the way that SQL Server handles passwords. For more information, see the "Effect on Passwords of Changing Sort Orders" topic in SQL Server 7.0 Books Online.
• When you run the output from the sp_help_revlogin script on the destination server, if the destination server already has a login defined with the same name as one of the logins on the script output, you may see the following error upon execution of the output of the sp_help_revlogin script:
Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 56
The login 'test1' already exists.
Likewise, if a different login exists with the same SID value on this server as the one you are trying to add, you receive the following error message:
Server: Msg 15433, Level 16, State 1, Procedure sp_addlogin, Line 93
Supplied parameter @sid is in use.
Therefore, you must carefully review the output from these commands, examine the contents of the sysxlogins table, and address these errors accordingly.
• The SID value for a particular login is used as the basis for implementing database level access in SQL Server. Therefore, if the same login has two different values for the SID at the database level (in two different databases on that server), the login will only have access to that database whose SID matches the value in syslogins for that login. Such a situation might occur if the two databases in question have been consolidated from two different servers. To resolve this problem, the login in question would have to be manually removed from the database that has a SID mismatch by using the sp_dropuser stored procedure, and then added again by using the sp_adduser stored procedure.
Back to the top
--------------------------------------------------------------------------------
APPLIES TO
• Microsoft SQL Server 7.0 Standard Edition
• Microsoft SQL Server 2000 Personal Edition
• Microsoft SQL Server 2000 Standard Edition
• Microsoft SQL Server 2000 Workgroup Edition
• Microsoft SQL Server 2000 Developer Edition
• Microsoft SQL Server 2000 Enterprise Edition
• Microsoft SQL Server 2005 Standard Edition
• Microsoft SQL Server 2005 Workgroup Edition
• Microsoft SQL Server 2005 Developer Edition
• Microsoft SQL Server 2005 Enterprise Edition
Back to the top
Keywords: kbhowtomaster kbinfo KB246133
Back to the top
Subscribe to:
Posts (Atom)