Migrating Logins from One SQL Server to another server

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.