SQL Server (7.0 & 2000) Login & Password Transfer

A database administrator can transfer his/her SQL server database from one to another server for the better performance or any other reasons. After transferring the database, database administrator may not be able to login into new server and got below error message:

Msg 18456, Level 16, State 1
Login failed for user '%ls'.

Above error message occurs due to incorrect logins & passwords. To fix this error message you have to transfer the logins and passwords to the new server.

How to Transfer Logins & Passwords: SQL server offers different transfer procedures for the different SQL server database like SQL server 7.0, 2000, 20005 & 2008.

Case1: If both source and designation servers are running SQL server 7.0 then you can use transfer feature of data transformation services (DTS) to transfer logins & passwords between two servers.

        Source Server                 Designation Server
        SQL server 7.0                SQL server 7.0


Note: It does not transfer logins and passwords for the SQL server authenticated logins.

Case2: If source and designation servers are SQL server 2000 or source is SQL server 7.0 and designation server is SQL server 2000 then you can use new data transformation services package to transfer logins and passwords. To do this, follow the below steps:

        Source Server                    Designation Server
        SQL server 2000                SQL server 2000
                                     OR
        Source Server                 Designation  Server
        SQL server 7.0                SQL server 2000   

• Connect to SQL server 2000 designation server
• Move DTS in SQL server enterprise manager
• Explore the folder
• Now right click on local packages
• Then click on the new package
• Now DTS package has been opened
• Goto the task menu and click on transfer logins Task
• Finally complete the task

Notification: There are more another cases for SQL server 2005 & 2008 that will discuss in the next article.