Database Users and Server Logins and the Mapping between them

Question

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?

Answer 1

Logins or server logins exists at the SQL server level, and the database user is at the database level. They need not have the same name, but generally do. In an instance, you cannot have a database user function properly without a corresponding SQL server login.

Let us say you have an instance: instanceA with a sever login userA on your computer. You can map this server login userA to a database user: userA for a database DB_A. They are both named userA, but one is the server login and one is the database user. The database user: userA resides in DB_A. When you backup and restore DB_A from instanceA to another instanceB, the database user: userA is moved, but the server login: userA still remains on instanceA. To move the logins from instanceA to instanceB, you can use sp_help_revlogin (Click to check out KB page).

If you had previously created the SQL server login userA on instanceB, then SQL will try to map this login with database user: userA, when DB_A is restored from instanceA to instanceB. If this fails, you can use the sp_change_users_login (Click for BOL link) procedure to fix the broken mappings. Hope this clears things up.