-- List any User/Group profiles that need creating/reinstating
PRINT 'Cut&Paste the following code and EXECUTE any appropriate snippets'
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)+LEFT('-- EXEC ' + db_name() 
	+ '.dbo.sp_dropuser @name_in_db = '
	+ '''' + U.name + '''	-- Remove if access no longer require to this DB', 200),
	CHAR(13)+CHAR(10)+LEFT('EXEC ' + db_name() 
	+ '.dbo.sp_change_users_login ''Update_One'', '
	+ '''' + U.name + ''', '
	+ '''' + U.name + '''', 90),
	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] COLLATE SQL_Latin1_General_CP1_CI_AS
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')