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