I have an instance of SQL Server 2008 R2 Express Edition at home and another on a server at a clients premises which I am using to develop an application using Access as the front end. This application has 5 separate logins which I can use in the connection string (based on the Access Group the user is in). The home instance has several databases – some of which have nothing to do with this applications
I have manually created the logins in each instance over time (and probably in a different order).
I am hoping to be able to (more than once) take a backup from my home machine of the database I am working on and load it on the instance at the clients premises using restore, just moving the files in the backup set to the actual files used on this server during the restore process. The piece I don't understand is the mapping between the database user and the login.
I had thought that the correspondence of name should be enough, but in trying to fix an issue on which users of the restored database didn't seem to be mapped to the server login I came across the Transact-SQL ALTER USER statement and in particular its reference to using WITH LOGIN and some discussion about a "SID".
Since (I presume) the Server Login names have difference SID's in each instance, does it mean that after I load a backup between instances that I have to reconnect the mapping again with a series of ALTER USER WITH LOGIN commands?