A number of databases currently running on SQL Server 2000 are been upgraded to a new 2005 installation, this also requires moving the existing logins. Several options are available to achieve this but I found the method below to be the most straightforward.
Firstly make a backup of the existing database and restore it on the destination Server.
Next create a project in Integration Services (IS) and use the ‘transfer logins task’ to move the logins, to complete this the following properties need populating :
- Setup source connection
- Setup destination connection
- for LoginsToTransfer Specify SelectedLogins
- Choose the logins to move
- Save and Execute the task
Ensure all the logins on the source server have all their attributes populated. When I initially ran the the IS package I got the error message “This property may not exist for this object, or may not be retrievable due to insufficient access rights” I traced the cause to not having a default database associated with the login, this error message appeared for all logins that were missing the attribute, even if they weren’t been moved.
Once the IS task completes successfully;
On the destination server, for each of the SQL Server logins Execute the following TSQL code. I did not have to do this for the NT logins.
USE <Database Name>
EXEC sp_change_users_login ‘UPDATE_ONE’, ‘<username>‘, ‘<login name>‘