When you want to move a database from one server to another, you can take a backup of the database and restore into the other server. However this will not transfers the logins from one SQL server to another, say SQL Server 2005 to SQL Server 2008. Instead users may receive the following message on SQL Server 2008, when they try to log in:
Login failed for user '<user name>'. (Microsoft SQL Server, Error: 18456)
To transfer the logins, the most reliable method in my opinion is to make use of the sp_help_revlogin stored procedure shown in this article http://support.microsoft.com/kb/918992
Assuming you have copied the script from the above link, just run the following code in SQL Server 2005
This command will generate the create logon script based on the logins available in the SQL Server 2005. Here’s a sample:
All you need to do now is go to the SQL Server 2008 instance, start SQL Server Management Studio, connect to the instance where you moved the database and execute the login script you generated in the previous step.
Note: If the logins should be transferred to a server with a different domain, you may need to specify the domain name in the script generated.