Wayward Users in SQL 2000/2005/2008

After migrating a database between database servers (such as between a 2000 and 2005 SQL server instance), user relationships at the server and database level tend to become disjointed.

After backing up and then restoring a database on a new SQL 2000 or 2005 server, users may lose their access even though the user appears to be defined at both the server and database levels. However, the GUID relationship between these levels has been broken, and these may no longer be considered the same people. Here is a quick way to find out if your user relationships have become fractured:

Run this command: EXEC sp_change_users_login 'Report'

A list of users will be returned at are either not defined at the server level, or don’t have a relationship with the identically-named account at the server level:

walterw	0x52C270B1CB7BD9008127009027I88F04
girtrudep	0x71502CC34E5BD4118678009027I88F04
juanq	0xB02589B294564D51180F1009027I88F04

For each of these users that are already defined at the server level (but just don’t happen to have a relationship with these database-level accounts), run this command:

EXEC sp_change_users_login 'Auto_Fix', 'usernamehere'

Or, if the account doesn’t exist server-wide yet, then run this:

EXEC sp_change_users_login 'Auto_Fix', 'usernamehere', 'loginnamehere', 'passwordhere'

If you run EXEC sp_change_users_login 'Report' again, you should see that the users are absent from the report, and all should be well.