if exists (select * from sysobjects where id = object_id('dbo.sp_Password_Scriptor') and sysstat & 0xf = 4)
	drop procedure dbo.sp_Password_Scriptor
GO


/*
Date: 04/18/01
Author: Claude Gabriel
Title: dbasp_UserPwds
Purpose:  I have been upgrading databases from SQL 6.5 to 7.0. 
I copy the database from production to a separate 6.5 / 7.0 
upgrade server. Then using this stored procedure I migrate the 
passwords to the upgrade server.  When running the upgrader, you
can select to upgrade server components. This will bring the 6.5 
passwords onto the 7.0 server. From there you can use the stored 
procedure in the MS Knowledge Base article at

	http://support.microsoft.com/support/kb/articles/Q246/1/33.ASP

This SQL Server 6.5 stored procedure can be used to 
create a script to migrate passwords from one server to another. 
Create a stored procedure in the Master database with this code.
Run this stored procedure from the user database.  It will create
a script to run on the server where you want to update passwords.

Notes:  
	1. The server configuration Allow Updates must be set to 1 on the
destination server to enable the generated script to update passwords.

*/

CREATE PROCEDURE sp_Password_Scriptor AS

set nocount on

declare @LN varchar(50)
declare @PWD varchar(50)
declare @PStr1 varchar(100)
declare @PStr2 varchar(100)
declare @PStr3 varchar(100)

create table #TempLogins
(LN varchar(50),
	PWD varchar(50) null )

insert #TempLogins
	select l.name,l.password
	FROM sysusers s
		join master..syslogins l
	on s.suid = l.suid
	where l.name is not null

insert #TempLogins
	select l.name,l.password
	FROM sysalternates s
		join master..syslogins l
	on s.suid = l.suid
	where l.name is not null



declare UpdatePWD cursor for select LN,PWD from #TempLogins 
open UpdatePWD
   fetch next from UpdatePWD into @LN, @PWD
      while (@@fetch_status <>-1)
	begin
	   select @PStr1 = 'Update SysLogins'
	   select @PStr2 = 'Set password = ''' + @PWD + ''''
	   select @PStr3 = 'Where name = ''' + @LN + ''''
	   PRINT @Pstr1
	   PRINT @Pstr2
	   PRINT @Pstr3
	   Print 'GO'
	   PRINT ' '

	fetch next from UpdatePWD into @LN, @PWD
      end
deallocate UpdatePWD 
drop table #TempLogins
GO
set nocount on