SET NOCOUNT ON
SELECT	DISTINCT 
	CASE WHEN L.sid IS NOT NULL 
		THEN '-- User ' + COALESCE(U.[name], '[NULL]') + ' already exists on server'
		ELSE 'EXEC sp_addlogin ''' + U.name + ''', ''password'', ''' + db_name() + '''' 
			+ CHAR(9) + '-- Only add if required!!'
		END,
	CHAR(13)+CHAR(10)+'-- EXEC ' + db_name() 
	+ '.dbo.sp_dropuser @name_in_db = '
	+ '''' + U.name + '''	-- Remove if access no longer require to this DB',
	CHAR(13)+CHAR(10)+'EXEC ' + db_name() 
	+ '.dbo.sp_change_users_login ''Update_One'', '
	+ '''' + U.name + ''', '
	+ '''' + U.name + '''',
	CHAR(13)+CHAR(10)+ '-- ========== --'
FROM	sysusers U
	LEFT OUTER JOIN 
		(
			sysmembers M 
			INNER JOIN sysusers G 
				ON G.uid = M.groupuid
		) ON M.memberuid = U.uid
	LEFT OUTER JOIN master.dbo.syslogins L 
		ON L.[name] COLLATE SQL_Latin1_General_CP1_CI_AS = U.[name]
WHERE	    U.islogin = 1 
	AND U.isaliased = 0 
	AND U.hasdbaccess = 1
	AND 
	(
		   G.issqlrole = 1 
		OR G.uid IS NULL
	)
	AND U.name NOT IN ('dbo')