Script to fix Orphaned users after restoring (SQL 2005 , 2008 , 2008 R2)

This is a very useful script I've been using to troubleshoot and fix Orphaned users in sql after restoring databases to a testing environment.


I do restore my production databases to a test server on a daily basis so at the end of the process I run this script. It remaps the orphan database users to the sql logins (the sql logins must be created beforehand) 

use master

go

IF (object_id( 'tempdb..##TMP_ORPHANED_USERS' ) IS NOT NULL) DROP TABLE ##TMP_ORPHANED_USERS ;

CREATE TABLE ##TMP_ORPHANED_USERS(

ServerName sysname

,DatabaseName sysname

,UserName varchar(500)

,UserType varchar(500)

,DefaultSchemaName varchar(500)

,ResolvedID varchar(500)

)

DECLARE @db sysname,

  @cmd varchar(4000)

SET NOCOUNT ON

DECLARE C_databases CURSOR FOR

SELECT name FROM sys.databases (NOLOCK)

WHERE database_id >4 and is_read_only = 0

order by name

OPEN C_Databases

FETCH NEXT FROM C_Databases

INTO @db

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @cmd = 'USE ['+@db+'];INSERT INTO ##TMP_ORPHANED_USERS select @@servername,db_name(),[name],type_desc,Default_Schema_Name,SUSER_SNAME(SID) from sys.database_principals

where type_desc IN (''SQL_USER'',''WINDOWS_USER'',''WINDOWS_GROUP'') and

principal_id >4 and

SID not in (select SID from sys.server_principals)

and [name] <> ''guest'''

 --PRINT @cmd

--PRINT ''

EXEC (@cmd)

FETCH NEXT FROM C_Databases

INTO @db

END

CLOSE C_Databases

DEALLOCATE C_Databases

select * from ##TMP_ORPHANED_USERS

GO

DECLARE @db sysname,

 @user sysname,

  @defaultschema varchar(500),

  @cmd varchar(4000)

SET NOCOUNT ON

DECLARE C_OrpUsers CURSOR FOR

SELECT DatabaseName,UserName,DefaultSchemaName FROM ##TMP_ORPHANED_USERS (NOLOCK)

OPEN C_OrpUsers

FETCH NEXT FROM C_OrpUsers

INTO @db,@user,@defaultschema

WHILE @@FETCH_STATUS = 0

BEGIN

--SELECT @cmd = 'USE '+@db+';IF EXISTS (select * from sys.schemas where name = '''+@user+''')DROP SCHEMA ['+@user+'];DROP USER ['+@user+'];'

 SELECT @cmd = 'USE '+@db+';ALTER USER ['+@user+'] WITH DEFAULT_SCHEMA =['+@defaultschema+'],LOGIN = ['+@user+']'

 BEGIN TRY

  PRINT @cmd

  EXEC (@cmd)

END TRY

BEGIN CATCH

  SELECT

   @db as DatabaseName

   ,@User as UserName

   ,ERROR_NUMBER() AS ErrorNumber

   ,ERROR_SEVERITY() AS ErrorSeverity

   ,ERROR_STATE() AS ErrorState

   ,ERROR_PROCEDURE() AS ErrorProcedure

   ,ERROR_LINE() AS ErrorLine

   ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

FETCH NEXT FROM C_OrpUsers

INTO @db,@user,@defaultschema

END

CLOSE C_OrpUsers

DEALLOCATE C_OrpUsers

IF (object_id( 'tempdb..##TMP_ORPHANED_USERS' ) IS NOT NULL) DROP TABLE ##TMP_ORPHANED_USERS ; 

GO