This short entry is simply a discussion of what happens when you attach a populated database to a different SQL Server. I do this all the time when I build demo systems or move test databases into production.
Consider that all of the objects in a database have a name and (more importantly) a digital ID--an number that uniquely identifies it from the others. When you create a database it gets a name and a object ID. While simplified, this basically means that when you create a new Login, it gets a unique number (the SID). When you create a new User in a database, it also gets a UID and its own SID. When you attach a database that already has users (as most do), the users probably won't match the Logins already stored in master. While it would be possible drop and recreate all of the users, it's easier to simply run a SP to fix up the mismatched User Names and the User IDs associated with them. In recent versions of SQL Server, this data is managed in the user database system.database_principals system catalog view.
The trick is to reset the values stored in the "principals" system table with the SQL Server login of the same name.
First, I suggest you try sp_change_users_login
This system stored procedure maps an existing database user to a SQL Server login. However, they say this sp will be removed in a future version of Microsoft SQL Server so it's probably a good idea to avoid using this approach in new development work, and plan to modify applications that currently use this feature. It still works in SQL Server 2008.
If you decide to use sp_change_users_login, I would take advantage of its "Auto_Fix" option to fix up the login referenced.
To resync the "Admin" database user with the "Admin" SQL Server login, I executed the following TSQL. No, you don't need to pass a password.
EXEC sp_change_users_login 'Auto_Fix', 'admin';
(1 row(s) affected)
The row for user 'admin' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.
This SP needs to be re-executed for each user in the database that's associated with a named SQL Server Login.
Next, you might try ALTER USER instead. However, there does not seem to be any documented way to re-sync the principles system table and an existing login.
Hi Bill -
I think that sp_change_users_login is great for one-time movements but a real PIA for temporary movements (i.e. log shipping or mirroring in which you eventually plan to "fail back" to the source/primary server). The problem with sp_change_users_login in a fail-back scenario is that, when sp_change_users_login creates a new login (AUTO_FIX), (1) it creates the login with a different SID than the original login, and (2) it then remaps the user to match the new SID.
Again I think that sp_change_users_login is great for one time movements but sp_help_revlogin (http://support.microsoft.com/kb/246133) is a better choice for temporary movements since it creates the login with the same SID as the source.