SQL Server Performance Forum – Threads Archive

Login Transfer from 2000 to 2005

Hello All, We are trying to migrate the databases from 2000 to 2005 and for migrating logins we used below method.
I have taken the output of sp_help_revlogin from SQL 2000 and executed the output on SQL 2005 server, we got many errors like
Server: Msg 15433, Level 16, State 1, Procedure sp_addlogin, Line 93
Supplied parameter @sid is in use.
we have checked the logins and all the logins were created on SQL Server 2005 but we are unable to connect to the server using SQL authentication. Can some body help me what may be the reason for this error and is the way i tried is correct or not? if not what is the best way to migrate logins. Thanks in advance, Regards,
Mahi

The following was tested from 7.0 to 2000. Don’t know from 2000 to 2005. 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 & 204<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> = 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 & 204<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> = 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 ———————————————————————— It also needs sp_hexadecimal: 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 Luis Martin Moderator SQL-Server-Performance.com All in Love is Fair Stevie Wonder All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks Luis Martin… Any one please help me for 2000 to 2005 Thanks,
Mahi Mahi

Look at great article with script in "How to transfer logins and passwords between instances of SQL Server" You can transfer logins and passwords from SQL Server 2000 to SQL Server 2005. http://support.microsoft.com/kb/246133/</a> [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Deepak Kumar MVP, MCDBA – SQL Server Disclaimer: This post is provided as is with no rights & warranty for accuracy, for the sake of knowledge sharing only.

SP_HELPREVLOGIN will still work on SQL 2005, as it worked for me. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

Hi Satya, even i too got all the logins by using sp_help_revlogin but no SQL Server login is working fine, i reset one SQL login and now that is working fine but there are many SQL logins and changing the passwords and informing all the users is not possible. is there any other to work all SQL logins with out resetting them. Thanks,
Mahi Mahi

It sounds like you are trying to add new logins using the sp_help_revlogin script to a new server where there are already logins that have the same SIDs Instead, you might elect to "fix" the orphaned users on the new server using sp_change_users_login. That will link the user object in the DB to a login having the same name as on the old box but with a different SID. Just had to do that myself, yesterday <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

before executing the out of sp_help_revlogin out put on new server there were only 10 logins i think and after execting the output it created all the logins(approx 300) but by give the error "Supplied parameter @sid is in use". i have checked the Orphand users and there are no users orphaned, it seems all the logins we created but the thing is we are not able to login to the server with SQL authentication its giving an error like password is incorrect even after typing the correct password. if we reset the password then we are able to connect to the server using SQL authentication. Thanks,
Mahi Mahi

]]>