How should I handle mismatched SQL Server logins? Can you explain the differences between Data Transformation Services (DTS) and SQL Server Integration Services (SSIS)?
SQL Server stores LOGIN information in the MASTER database and USER information in the user databases. Because of this, when you restore a database from a different server or if you attach a database from a different server, you will get mismatches unless the servers were set up and administered the same way. If you are moving a database to a brand new server where no login information yet exists, you can script out login information and apply it to the new server. This can be done using DTS or it can be done by writing T-SQL code to script out the data. This MSDN article goes into more detail on how this can be accomplished. Also refer to sp_change_users_login in SQL Server Books Online. This stored procedure gives you information on mismatches as well as allows you to fix the mismatched data. To identify and clean up orphaned users, you can write T-SQL code that will identify the users that do not have a corresponding login and then use the sp_revokedbaccess stored procedure to drop
