Presents your 
SQL SERVER E-NEWSLETTER for July 9, 2002

<------------------------------------------->

USE SP_CHANGE_USERS_LOGIN WHEN RESTORING DATABASES

When restoring a database on a server different than the one on which
the original database resides, ensure that the users in the database
correspond to the correct login in the master database.

The procedure that will fix these mappings is sp_change_users_login.
There are three arguments, the first of which is mandatory. The first
argument is the action, which may be one of three values: Auto_Fix, 
Report, or Update_One. The second argument is the database username 
or pattern. The last argument is the login name.

The Report action will display the users orphaned from the syslogins in
the master database. You should run the following command on the database
you restored to a different server (the server on which the original
database did not reside).

EXEC sp_change_users_login Report

The Update_One parameter for sp_change_users_login will adjust the
database system tables with the master system tables to be synchronized for
the one user. When using the Update_One parameter, all parameters or
arguments are required with the sp_change_users_login system procedure. The
following command can be run when a standard SQL user and login, MyUser, is
updated after a restore.

EXEC sp_change_users_login Update_One, MyUser, MyUser

The Auto_Fix action will allow SQL Server to make the best effort to
automatically fix the orphaned user from the logins in the master database.
Auto_Fix requires the second argument, @UserNamePattern.

The following script is a command you can execute when fixing a standard
SQL user, MyUser, after a restore.

EXEC sp_change_users_login Auto_Fix, MyUser

This procedure won't fix the database owner if it was originally changed
from sa. You will need to execute the procedure sp_changedbowner for the
database owner to change to the correct user or login.

----------------------------------------