Login Transfer from 2000 to 2005 | SQL Server Performance Forums

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,
The following was tested from 7.0 to 2000. Don’t know from 2000 to 2005.<br /><br />CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS<br />DECLARE @name sysname<br />DECLARE @xstatus int<br />DECLARE @binpwd varbinary (256)<br />DECLARE @txtpwd sysname<br />DECLARE @tmpstr varchar (256)<br />DECLARE @SID_varbinary varbinary(85)<br />DECLARE @SID_string varchar(256)<br /><br />IF (@login_name IS NULL)<br />DECLARE login_curs CURSOR FOR<br />SELECT sid, name, xstatus, password FROM master..sysxlogins<br />WHERE srvid IS NULL AND name &lt;&gt; ‘sa'<br />ELSE<br />DECLARE login_curs CURSOR FOR<br />SELECT sid, name, xstatus, password FROM master..sysxlogins<br />WHERE srvid IS NULL AND name = @login_name<br />OPEN login_curs<br />FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd<br />IF (@@fetch_status = -1)<br />BEGIN<br />PRINT ‘No login(s) found.'<br />CLOSE login_curs<br />DEALLOCATE login_curs<br />RETURN -1<br />END<br />SET @tmpstr = ‘/* sp_help_revlogin script ‘<br />PRINT @tmpstr<br />SET @tmpstr = ‘** Generated ‘<br />+ CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */'<br />PRINT @tmpstr<br />PRINT ”<br />PRINT ‘DECLARE @pwd sysname'<br />WHILE (@@fetch_status &lt;&gt; -1)<br />BEGIN<br />IF (@@fetch_status &lt;&gt; -2)<br />BEGIN<br />PRINT ”<br />SET @tmpstr = ‘– Login: ‘ + @name<br />PRINT @tmpstr<br />IF (@xstatus & 4) = 4<br />BEGIN — NT authenticated account/group<br />IF (@xstatus & 1) = 1<br />BEGIN — NT login is denied access<br />SET @tmpstr = ‘EXEC master..sp_denylogin ”’ + @name + ””<br />PRINT @tmpstr<br />END<br />ELSE BEGIN — NT login has access<br />SET @tmpstr = ‘EXEC master..sp_grantlogin ”’ + @name + ””<br />PRINT @tmpstr<br />END<br />END<br />ELSE BEGIN — SQL Server authentication<br />IF (@binpwd IS NOT NULL)<br />BEGIN — Non-null password<br />EXEC sp_hexadecimal @binpwd, @txtpwd OUT<br />IF (@xstatus & 204<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> = 2048<br />SET @tmpstr = ‘SET @pwd = CONVERT (varchar(256), ‘ + @txtpwd + ‘)'<br />ELSE<br />SET @tmpstr = ‘SET @pwd = CONVERT (varbinary(256), ‘ + @txtpwd + ‘)'<br />PRINT @tmpstr<br />EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT<br />SET @tmpstr = ‘EXEC master..sp_addlogin ”’ + @name<br />+ ”’, @pwd, @sid = ‘ + @SID_string + ‘, @encryptopt = ‘<br />END<br />ELSE BEGIN<br />– Null password<br />EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT<br />SET @tmpstr = ‘EXEC master..sp_addlogin ”’ + @name<br />+ ”’, NULL, @sid = ‘ + @SID_string + ‘, @encryptopt = ‘<br />END<br />IF (@xstatus & 204<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> = 2048<br />– login upgraded from 6.5<br />SET @tmpstr = @tmpstr + ”’skip_encryption_old”'<br />ELSE<br />SET @tmpstr = @tmpstr + ”’skip_encryption”'<br />PRINT @tmpstr<br />END<br />END<br />FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd<br />END<br />CLOSE login_curs<br />DEALLOCATE login_curs<br />RETURN 0<br /><br />GO<br />————————————————————————<br />It also needs sp_hexadecimal:<br />CREATE PROCEDURE sp_hexadecimal<br />@binvalue varbinary(256),<br />@hexvalue varchar(256) OUTPUT<br />AS<br />DECLARE @charvalue varchar(256)<br />DECLARE @i int<br />DECLARE @length int<br />DECLARE @hexstring char(16)<br />SELECT @charvalue = ‘0x'<br />SELECT @i = 1<br />SELECT @length = DATALENGTH (@binvalue)<br />SELECT @hexstring = ‘0123456789ABCDEF'<br />WHILE (@i &lt;= @length)<br />BEGIN<br />DECLARE @tempint int<br />DECLARE @firstint int<br />DECLARE @secondint int<br />SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))<br />SELECT @firstint = FLOOR(@tempint/16)<br />SELECT @secondint = @tempint – (@firstint*16)<br />SELECT @charvalue = @charvalue +<br />SUBSTRING(@hexstring, @firstint+1, 1) +<br />SUBSTRING(@hexstring, @secondint+1, 1)<br />SELECT @i = @i + 1<br />END<br />SELECT @hexvalue = @charvalue<br /><br />GO<br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">All in Love is Fair <br />Stevie Wonder<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
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"<br /><br />You can transfer logins and passwords from SQL Server 2000 to SQL Server 2005. <br /><br /><br /<a target="_blank" href=http://support.microsoft.com/kb/246133/>http://support.microsoft.com/kb/246133/</a><br /><br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><b>Deepak Kumar</b><br /><font size="1">MVP, MCDBA – SQL Server<br /><br />Disclaimer: This post is provided as is with no rights & warranty for accuracy, for the sake of knowledge sharing only.</font id="size1">
SP_HELPREVLOGIN will still work on SQL 2005, as it worked for me. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
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<br /><br />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.<br /><br />Just had to do that myself, yesterday <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />
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