Moving SQL Server user accounts

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 :

imageimage

 

  1. Setup source connection
  2. Setup destination connection
  3. for LoginsToTransfer Specify SelectedLogins
  4. Choose the logins to move
  5. 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>
GO
EXEC sp_change_users_login ‘UPDATE_ONE’, ‘<username>‘, ‘<login name>
GO

 

References

http://sqlblog.com/blogs/eric_johnson/archive/2008/10/17/fixing-orphaned-users.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s