Restore Database 2005 User Logins

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

A WordPress.com Website.

Up ↑

%d bloggers like this: