Complete Solution for transferring logon and password between different versions of SQL Server

Complete Solution for transferring logon and password between different versions of SQL Server
Complete Solution for transferring logon and password between different versions of SQL Server
This method applies to the following situations:
Transmit the logon and password from SQL Server 7.0 to SQL Server 7.0.
Transmit the logon and password from SQL Server 7.0 to SQL Server 2000.
Transmit the logon and password from SQL Server 7.0 to SQL Server 2005.
Transfer the login and password between the servers running SQL Server 2000.
Transmit the logon and password from SQL Server 2000 to SQL Server 2005.

Note: Please refer to the remarks at the end of this article to learn important information about the following steps.

To transfer the login and password between different versions of SQL Server, follow these steps: 1. Run the following script on the source SQL Server. This script creates two stored procedures in the master database: sp_hexadecimal and sp_help_revlogin. Proceed to Step 1 after the process is created.

Note: The following process depends on the SQL server system table. The structure of these tables may change between different versions of SQL Server. Do not select them directly from the system table.
----- 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
declare @ firstint int
declare @ secondint
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
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
Select Sid, name, xstatus, password from Master .. sysxlogins
Where srvid is null and name <> 'sa'
Else
Declare login_curs cursor
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 'Clare @ 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 creating the sp_help_revlogin stored procedure, run the sp_help_revlogin process from the query analyzer on the source server. Sp_help_revlogin stored procedures can be used for both SQL Server 7.0 and SQL Server 2000. The output of the sp_help_revlogin stored procedure is a logon script that creates a logon with the original Sid and password. Save the output, paste it to the query analyzer on the target SQL Server, and run it. For example:
Exec master.. sp_help_revlogin

Remarks
? Check the script carefully before running the script on the target SQL Server. If you must transfer the login to an SQL server instance that is not in the same domain as the SQL Server Source instance, edit the script generated by the sp_help_revlogin process and replace the domain name with the new domain name in the sp_grantlogin statement. Because the integrated login granted access in the new domain has different Sid from the login in the original domain, database users will be isolated from these logins. To solve these isolated users, viewArticle. If the SQL server instance in the same domain is used for integrated login, the same SID is used, and the user is unlikely to be isolated.
? After a mobile login, the user no longer has the permission to access the database that has been moved at the same time. This issue is called "isolated users ". If you attempt to grant the permission to access this database to log on, it may fail. This indicates that the user already exists:
Microsoft SQL-DMO (ODBC sqlstate: 42000) Error 15023: user or role '% s' already exists in the current database.

? The reason for this is that the @ encryptopt parameter in the sp_addlogin system stored procedure allows you to create a login by using an encrypted password. For more information about this process, 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. End users cannot create or run these stored procedures unless the members of the SysAdmin role grant the necessary permissions.
? This method does not try to transmit the default database information for a specific login, because the default database does not always exist on the target server. To define a default database for a login, you can use the sp_defaultdb system stored procedure by passing the login name and default database as parameters to the process. For more information about using this process, see the "sp_defaultdb" topic in SQL Server books online.
? In the process of transferring logon between SQL Server instances, if the sorting order of the source server is case insensitive and the sorting order of the target server is case sensitive, after the login is transferred to the target server, all letter characters in the password must be entered in uppercase or lowercase. If the sorting order of the source server is case-sensitive, but the sorting order of the target server is case-insensitive, you cannot log on using the transferred logon as described in this document, unless the original password does not contain letters or all letters in the original password are uppercase characters. This issue does not occur if both servers are case-sensitive or both are case-insensitive. This is a side effect of SQL Server's password processing method. For more information, see the topic "effect on passwords of changing sort orders" in SQL Server 7.0 books online (change the effect of sorting order on password.
? When running the output of the "sp_help_revlogin" script on the target server, if the target server has defined a login and the login name is the same as a login name in the Script output, when executing the output of the "sp_help_revlogin" script, you may see the following error:
Server: MSG 15025, level 16, state 1, procedure sp_addlogin, line 56
The login 'test1' already exists.
Similarly, if another login exists on the server and its Sid value is the same as the SID value of the login you want to add, the following error message will be received:
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 of these commands, check the contents of the sysxlogins table, and resolve these errors accordingly.
? The SID value of a specific logon is used as the basis for database-level access in SQL Server. Therefore, if the same login has two different Sid values at the database level (two different databases on the server, then, this login will only be able to access the database whose Sid matches the value in syslogins of the login. This situation may occur if the two databases discussed have been merged from two different servers. To solve this problem, you must use the sp_dropuser stored procedure to manually delete the discussed logon from a database with mismatched Sid, and then use the sp_adduser stored procedure to add it.