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?
asked Oct 25, 2010 at 02:06 AM in Default
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
Alternatively script the whole user deletion and recreation for the server level.
adding to kev Rileys answer
you can also use sp_change_users_login procedure to fix orphan user issuess The following example shows how to map an existing userA to a login of the same name.
answered Oct 25, 2010 at 02:27 AM
To find any 'orphaned users' you can use:
To fix the 'orphaned users' you can use:
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!
answered Oct 25, 2010 at 02:33 AM