Map SQL Logins after restore

Finally got around to sorting out orphaned users. Create two procedures... Here it is.

Put these procedures in the master database..

The second procedure loops thru each database (using the first procedure) and populates a temp table.The select statement at the end of GetOrphanedUsers_AllDatabases simply builds the string to execute.

--Here is the first proc.
CREATE Proc [dbo].[GetOrphanedUsers]
@databasename sysname
as
set nocount on

Create table #Fitz
(DB varchar(50),
OrphanedUserName varchar(100),
UserType varchar (50))

declare @sql varchar(1000)
set @sql = '
use '+@databasename+'
insert into #Fitz
(DB, OrphanedUserName, UserType)
SELECT db_name() as ''DB'', a.name AS OrphanUserName, a.type_desc AS UserType
FROM sys.database_principals a
LEFT OUTER JOIN sys.server_principals b
ON a.sid = b.sid
WHERE b.sid IS NULL
AND a.type In (''S'', ''U'', ''G'')
AND a.name NOT in (''sys'', ''INFORMATION_SCHEMA'', ''guest'')

select * from #Fitz
'

exec (@sql)
GO

----Second Proc
CREATE PROC [dbo].[GetOrphanedUsers_AllDatabases]
AS
Create table #OUsers
(DB varchar(50),
OrphanedUserName varchar(100),
UserType varchar (50))

DECLARE @databasename sysname
DECLARE DatabaseLoop CURSOR FOR
(select name from sys.databases where state=0 and database_id >4 )
OPEN DatabaseLoop
FETCH NEXT FROM DatabaseLoop into @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #OUsers
exec GetOrphanedUsers @databasename
FETCH NEXT FROM DatabaseLoop into @databasename
END
CLOSE DatabaseLoop
DEALLOCATE DatabaseLoop

Select 'exec ' + DB + '..sp_change_users_login ' + '''' + 'auto_fix' + '''' + ',' + '''' + OrphanedUserName + ''''--*
from #OUsers
where orphanedUserName <> 'dbo'
order by db
GO