ALTER USER ... WITH LOGIN to fix orphaned users

sp_change_users_login is deprecated.

From sql 2005 SP2, ALTER USER .... WITH LOGIN comes into play to achieve the same, i.e. remapping orphaned users to logins

ALTER USER Username WITH LOGIN = LoginName

I like to keep usernames and logins name the same where possible, hence -

ALTER USER Doermouse WITH LOGIN = Doermouse

MSDN : ALTER USER


Here is what works in SQL 2000 / 2005 -

Lists usernames that are not mapped to logins
exec sp_change_users_login 'report'

Map db username to server login if names match -

exec sp_change_users_login 'update_one', 'username'

Maps db username to server login if names match, If no login exists, it creates one with the password given.

exec sp_change_users_login 'auto_fix', 'username' , 'password'

Links -

USP_FixUsers - Works for all users in a db
USP_FixOrphans - Works for all users in all dbs on a server
Mapping SQL Server Logins to Database Users
Fix Orphaned Users SQL 2005
MSDN : Sp_change_users_login
MSDN : Deprecated Database Engine Features in SQL Server 2008 R2