Friday, January 9, 2009

02 - Data Migration Techniques



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'



sp_grantlogin @loginame = N'JOEDOMAIN\joey'


sp_grantlogin @loginame = N'JOEDOMAIN\marcy'




-          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.





-          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

No comments:

Post a Comment