Fixing Orphaned Database Users in SQL Server 2005

I see this on almost all SQL Server instances I work on - existing user in a database with dbo permissions yet they could not access anything in the database. This is because SQL Server doesn't really look at the user or login name but rather the SID values (I've talked about this in here). Maybe they restored a database back from a development server to the production server without synchronizing the logins. If this was SQL Server 2000, you'd probably end up dropping logins, recreating the logins using the script provided by Microsoft in this KB article and recreate the database users. Unfortunately, this is not a straight-forward task. Good thing Microsoft has provided the sp_change_users_login stored procedure to fix orphaned users. Using the UPDATE_ONE parameter will 're-map' an orphaned database user to an existing SQL Server login with the same name. That in itself will spare you the trouble of going thru the process mentioned above (although you will still have to worry about orphaned database users that own schemas which is very much common among databases that have been upgraded from SQL Server 7.0 and 2000). Below is a syntax for 're-mapping' an orphaned database user to an existing SQL Server login

EXEC sp_change_users_login 'UPDATE_ONE','user','user'
GO

You still need to run the stored procedure with the REPORT parameter to return a list of orphaned database user

EXEC sp_change_users_login 'Report'