When a database is restored from one server to another user logins are dropped. This script will help rectify that:
SET NOCOUNT ON
--SELECT 'EXEC sp_change_users_login ''Auto_Fix'', '''+ user_id + ''', NULL, '''+ user_id + ''''
SELECT 'EXEC sp_change_users_login ''Auto_Fix'', '''+ user_id + ''', NULL, ''password'''
FROM staff s
JOIN sys.sysusers su
ON su.name = s.user_id
WHERE s.is_Deleted = 'N'
This will generate some further SQL statements that need to be executed. The passwords are reset to the values in the final parameter (i.e. password):
EXEC sp_change_users_login 'Auto_Fix', 'wm', NULL, 'password'
EXEC sp_change_users_login 'Auto_Fix', 'TTestA', NULL, 'password'
EXEC sp_change_users_login 'Auto_Fix', 'TTestB', NULL, 'password'
EXEC sp_change_users_login 'Auto_Fix', 'TTestC', NULL, 'password'
To transfer logins between servers: http://support.microsoft.com/kb/246133
Mark Cooper 
Leave a Reply