Rebinding a SQL Server user to its login after a restore with this user

Question

After performing a restore of a SQL Server (v12.0.2000) database, I figured that a user needs to be remapped to its login (this issue seems to be known as orphan users). And one of the simplest way to do it is to perform a :

use [MyDatabase];
alter user MyUser with login = MyUser;

Nevertheless, just after achieving a restore, the USE command fails because I'm trying to execute the above command with 'MyUser', which is the one that is orphaned...

Because it is in a script, I would like to avoid performing a restore with one user and a alter user with another user, in order the first user be correctly remapped to its login.

Is there a way to do this with 'MyUser' (who could connect to master without problem) ?

I tried it by fully qualifying 'MyUser' the following way without success :

use master;
alter user MyDatabase.MyUser with login = MyUser;

Any idea if this is possible and how to do it ?

• Kin Shah: Your MyUser should have permissions e.g. it should be a db_owner or if it has restore rights, then it should be able to run alter user .... with login.

Answer 1

Unfortunately no. Remember that a database principal and a server principal are two separate objects. Until you fix the orphan the server principal MyUser has no access to MyDatabase even though there is an orphaned user called MyUser in the database.

The other way to fix an orphan is to change the SID of the server principal to match that of the database principal. This does have a risk of creating orphans in other databases and even more importantly in this case to do it you have to drop the principal and re-create it with the new SID. Which of course isn't possible using that particular ID.

Your only method is going to be to fix this using a separate id with the appropriate permissions. That could be an id with sysadmin permissions (which would have permissions to connect to the database even without an associated database principal) or an id that has DBO access on the database. Note those are examples, there are several other permissions available that will allow you to modify server/database principals.

Answer 2

You could use sp_change_users_login to update the database user's SID: https://msdn.microsoft.com/en-us/library/ms174378.aspx

Alternatively, you could script the login from the production server using sp_help_revlogin, drop the DR user and recreate it with the script: https://support.microsoft.com/en-us/kb/918992

• LowlyDBA - John M: sp_change_users_login is deprecated. It's recommended to use the ALTER LOGIN the OP mentioned as an alternative.
• Kenneth Fisher: Also remember that the user want's to do this as their existing user. Even using dbname.dbo.sp_change_users_login should fail with a permissions error the way they are trying to do it.