Hi, Something come up while I was restoring the Database this morning from one server to another server. There is a user called ***UserA*** and it has Database Fixed Role 'SetupAdmin' on *DB1* on *SVR1*. It is working fine whoever login as UserA. [This is **Production Server**] Same setup on *SVR2*, it has ***UserA*** with DB Fixed Role 'SetupAdmin' on *DB1*. [That server is **Dev-Server**] Restored **DB1** from **Production** to **Dev-Server**. UserA is no longer access into the DB1 on Dev-Server after restored until I need to login as 'sa' or something and set the permission again for UserA under 'User Mapping'. That is wasting the time for me to add the user 'UserA' every time I restored the Database. Anyway can we get around it? Thanks.
The issue is that the underlying identifier for UserA on SVR1, is not the same as the UserA on SVR2 - hence you get these 'orphaned users' whenever you restore a backup from another server. The simplest option would be to script the resolution and apply this at the end of your backup process. You are restoring a database, so I presume you are using an account that has permissions enough to do this. To de-orphan the users use ALTER USER UserA WITH LOGIN = UserA Alternatively script the whole user deletion and recreation for the server level.
+1 To Kev, his solution is the right way of doing it. I love the sp_change_users_login procedure that will be removed in a future release, and I want to show what we will miss when it's gone. To find any 'orphaned users' you can use: EXEC sp_change_users_login 'report' To fix the 'orphaned users' you can use: EXEC sp_change_users_login 'Auto_Fix', 'UserA', NULL, 'B3r12-3x$098f6'; GO But as I said before, this is a "deprecated feature" and is not for new development projects. Read more about it in [
http://msdn.microsoft.com/en-us/library/ms174378.aspx] Sorry for recommending something that is going to be removed! :