Reconnect all SQL logins after DB restore

Hi,

Below is a script that I use to reconnect sql logins after a database restore.

It reconnects all users to all databases.

Cheers

Use Master
GO

Set NoCount On


if exists(select * from tempdb..sysobjects where id = object_id('tempdb..#tblDatabases'))
	Drop Table #tblDatabases

if exists(select * from tempdb..sysobjects where id = object_id('tempdb..#tblUsers'))
	Drop Table #tblUsers



Declare @db nvarchar(max)
Declare @usr nvarchar(max)
Declare @sql nvarchar(Max)
Declare @msg nvarchar(200)

Create Table #tblDatabases
(
	DBName nvarchar(4000)
)

Create Table #tblUsers
(
	UserName nvarchar(4000)
)


Insert Into #tblDatabases
select name from master.dbo.sysdatabases where dbid > 4 
Order By name


Select Top 1 @db = DBname from #tblDatabases

While @db is not null
Begin
	set @msg = Char(13) + 'Processing ' + @db
	RAISERROR(@msg, 0, 0) WITH NOWAIT 
	
	set @sql = N'
	Use [' + @db + ']
	
	Declare @usr nvarchar(max)
	set @usr = null

	Truncate Table #tblUsers;
	Insert Into #tblUsers SELECT name from sysusers where altuid is null 
                 and name not in (''dbo'', ''guest'', ''sys'', ''INFORMATION_SCHEMA'');
	Select Top 1 @usr = userName from #tblUsers;
	
	While @usr is not null
	Begin
	
		print ''fixing user '' + @usr
		exec sp_change_users_login ''auto_fix'', @usr
		
		Delete from #tblUsers where username = @usr
		Set @usr = null
		Select Top 1 @usr = userName from #tblUsers	
	End'
	
	--print @sql
	exec(@sql)
	
	-- main loop
	Delete from #tblDatabases where DBName = @db;
	
	Set @db = null
	Select Top 1 @db = DBname from #tblDatabases
End

Drop Table #tblDatabases
Drop Table #tblUsers

Set NoCount Off