Home > SQL server DBA administration > Migrating Logins from One SQL Server to Another server during database Migration:

Migrating Logins from One SQL Server to Another server during database Migration:


In help of the article SQL Server Login Transfer by:  Tibor Karaszi I have made the document which can be very useful to our environment and might be somebody already has fair knowledge about the article then please ignores it.

As part of the normal work, a Database Administrator (DBA) will be required to migrate databases between servers. One of the reasons for a migration might be that we are moving an application from a quality assurance (QA) environment to a production environment.

There are a number of different ways to migrate SQL Server logins. We can manually re-enter the entire existing login on the new server. We can use DTS to transfer logins. There are probably a number of other ways to transfer logins. This article will discuss one of those other ways to streamline the migration of SQL Server logins,

SQL Server 2005 Data Transformation Services (DTS) includes a Transfer Logins task, but this task doesn’t cover all situations. The Transfer Logins task requires a network connection between SQL Server machines, which—for security reasons—isn’t always possible. Also, the task can transfer only from SQL Server 7.0 or 2000 to SQL Server 2000; it can’t transfer to pre-SQL Server 2000 releases.

Transfer logins between her SQL Server machines.

The following code shows how to script the sp_addlogin calls for the SQL Server logins:

SET NOCOUNT ON

SELECT ‘EXEC sp_addlogin @loginame = ”’ + loginname + ””

,’, @defdb = ”’ + dbname + ””

,’, @deflanguage = ”’ + language + ””

,’, @encryptopt = ”skip_encryption”’

,’, @passwd =’

, cast(password AS varbinary(256))

,’, @sid =’

, sid

FROM syslogins

WHERE name NOT IN (‘sa’)

AND isntname = 0

Each row in the column list is a column in the result. The script uses the isntname column to ascertain whether a login is a SQL Server login or an NT login. Executing the above code in a Query Analyzer grid shows that the binary data (and other parameters) are in separate columns. Also, because the password column is in Unicode (and encrypted), the code converts the password column into VARBINARY (256), so that you don’t lose characters.

The following code could use the following code to script the NT logins:

SELECT ‘EXEC sp_grantlogin @loginame = ”’ + loginname + ””

,’ EXEC sp_defaultdb @loginame = ”’ + loginname + ””

,’, @defdb = ”’ + dbname + ””

FROM syslogins

WHERE loginname NOT IN (‘BUILTIN\Administrators’)

AND isntname = 1

Save the output as a file and execute that file in the destination server.

 

You could get an error message if a login already exists. If you didn’t want to get the error messages, you could script an IF NOT EXISTS and a check against the login name column in the destination server for each call to sp_addlogin and sp_grantlogin.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: