question

I suppose you would like to go through this microsoft kb article and use the script they have given there.

http://support.microsoft.com/kb/246133

And if it is the same database, say you are trying to do a backup restore of a database from one server to another then the above will work for permissions as well.

This was discussed here: http://ask.sqlservercentral.com/questions/6115/when-should-i-transfrer-the-logins-and-passwords/6118#6118.

The important concept to understand is that everyone who accesses any database on a server does so through a login, which is at the server level and is stored in a system database. A login gets access to an individual database by being mapped onto a user, which is at the database level and is stored in that database. The database user has the ID of the server login. If you copy a login and database to another server you either have to ensure that you give the login the same ID - which the article Queue references will do - or you have to use sp_change_users_login to correct the login ID on the database user.

Just a reminder that sp_change_users_login should no longer be used, as it is deprecated by Microsoft, and that ALTER USER should be used instead. If copying users/Logins within the same Domain, life is simpler; however, if restoring databases from a PRODuction Server to say, a TEST Server in a separate Domain, then Windows users will have to be replaced (e.g. PROD\\userA doesn't exist in the TEST Domain: TEST\\userA.) Our developers, for example, have this Situation and have created a BackupRestore process that uses a BackupRestore database containing a **BackupRestore_Config** table and a **BackupRestore_User** table that saves the target db users, restores the PROD db to the TEST db Server, then Drops the invalid PROD db users and re-writes the saved target db users back to their respective databases and re-links the users to the Logins.