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
