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.

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

Cheers

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.