How to transmit logon and password between SQL Server instances

----- Begin script, create sp_help_revlogin procedure ----- use mastergoif object_id ('SP _ hexadecimal ') is not null drop procedure empty procedure sp_hexadecimal @ binvalue varbinary (256), @ hexvalue varchar (256) outputasdeclare @ charvalue varchar (256) Declare @ I intdeclare @ length intdeclare @ 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 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 endselect @ hexvalue = @ charvaluegoif object_id ('SP _ help_revlogin ') is not null drop procedure into gocreate procedure sp_help_revlogin @ login_name sysname = NULL asdeclare @ name empty @ xstatus intdeclare @ binpwd varbinary (256) Declare @ txtpwd empty @ 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_nameopen login_curs fetch next from login_curs into @ sid_varbinary, @ name, @ xstatus, @ binpwdif (@ fetch_status =-1) begin print 'no login (s) Found. 'Close login_curs deallocate login_curs return-1 endset @ tmpstr = '/* sp_help_revlogin script' print @ tmpstrset @ tmpstr =' ** generated '+ convert (varchar, getdate ()) + 'on' + @ servername + '*/' print @ tmpstrprint ''print 'descare @ 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 else begin -- SQL Server authentication IF (@ binpwd is not null) begin -- non-null password exec sp_hexad Ecimal @ 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 @ tmpstrexec sp_hexadecimal @ sid_varbinary, @ sid_string out set @ tmpstr = 'exec master .. sp_addlogin ''' + @ name + ''', @ PWD, @ SID = '+ @ sid_string +', @ encryptopt = 'end else begin -- null passwor Dexec 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 fetch next from login_curs into @ sid_varbinary, @ name, @ x Status, @ binpwd endclose login_curs deallocate login_curs return 0go ----- end script ----- 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. Example: exec master .. sp_help_revlogin

Original Website: Click to open the link