Transfer Logins from SQL Server 2005 to SQL Server 2008

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

EXEC sp_help_revlogin

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.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan