question

This isn't to transfer the Logins (i.e. with password, and from an existing server already set up), but it will

a) identify Users assigned to a Database b) Create new Server Logins for them if not found (you have to provide a password) c) "Connect" the database user record with the server login record

It doesn't make any actual changes, it outputs recommended SQL to be executed

I haven't used this script since SQL2000, so its possible it may need some changes on SQL 2005

            
USE MyDatabaseName            
GO            
-- ===== Reinstate User Logins after Restore ===== --            
-- 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)+'-- EXEC ' + db_name()             
    + '.dbo.sp_dropuser @name_in_db = '            
    + '''' + U.name + '''	'            
    + '-- Remove this user if access is no longer required to this DB',            
    CHAR(13)+CHAR(10)+'EXEC ' + db_name()             
    + '.dbo.sp_change_users_login ''Update_One'', '            
    + '''' + U.name + ''', '            
    + '''' + U.name + ''''            
FROM    dbo.sysusers AS U            
    LEFT OUTER JOIN            
    (            
    	dbo.sysmembers AS M             
    	INNER JOIN dbo.sysusers AS G             
    		ON G.uid = M.groupuid            
    ) ON M.memberuid = U.uid            
    LEFT OUTER JOIN master.dbo.syslogins AS L             
    	ON L.[name] = 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')            
-- ===== END OF Reinstate User Logins after Restore ===== --