02 - Data Migration Techniques

Hi,

 

Here we'll cover– How to Move Logins

 

-          Before moving or copying databases from other SQL Server instances, you should first move over the logins that have associated users in the databases.

-          SQL Server holds logins in the syslogins table (actually this is really a view that references the sysxlogins table) in the master database.

-          There are three different options for transferring logins between SQL Server servers, as detailed below.

 

 

Reference - To move logins and passwords from SQL Server 7.0 to SQL Server 2000, see Knowledge Base article Q246133 INF: How to Transfer Logins and Passwords Between SQL Servers

 

There are different methods to achieve the same, viz.

 

1.      Transact-SQL Script

2.      DTS Package Transfer Logins Task

3.      Scripting Out Logins and Passwords

 

 

 

Transact-SQL Script

 

-          The first option is available if you have kept a Transact-SQL script of all login present in your SQL Server 7.0 server.

-          For example, the following script adds users (with passwords), along with Windows-authenticated domain users:

 

 

Script of SQL Authentication and Windows Authentication users

sp_addlogin @loginame = N'JaneDoe', @passwd = '123#$', @defdb = N'pubs', @deflanguage = N'English'   sp_addlogin @loginame = N'JackSmith', @passwd = 'MeowMix1', @defdb = N'pubs', @deflanguage = N'English'   GO sp_grantlogin @loginame = N'JOEDOMAIN\joey' GO sp_grantlogin @loginame = N'JOEDOMAIN\marcy' GO

 

-          However, most DBAs will not have a complete script of login additions, so best to go for options 2 and 3.

 

 

DTS Package Transfer Logins Task

 

-          In Enterprise Manager, expand Server Group and the registration from which you wish to transfer logins.

-          This assumes you are using the SQL Server 2000 Enterprise Manager.

-          Expand the Data Transformation Services folder and right-click Local Packages. Select New Package.

-          In DTS Designer, select Task | Transfer Logins Task.

-          Continue with the wizard to complete the task.

 

 

Note-

 

-          Using this DTS method transfers passwords but not the original SID.

-          You still must fix orphaned login\database associations.

-          sysadmin permissions are required to use this method.

 

 

Scripting Out Logins and Passwords

 

Hope this helps.

 

 

Thanks & Regards,

Arun Manglick || Tech Lead