Congratulations to John Northfield, a SQL Server DBA for Quality Assurance and Design in Mount Waverley, Victoria, Australia, and Jason Watts, a SQL Server administrator for The Wellcome Trust in London. John won first prize of $100 for the best solution to the October Reader Challenge, "SQL Server Login Transfer." Jason won second prize of $50. Watch SQL Server Magazine UPDATE for next month's Reader Challenge. In the meantime, here's the solution to the October Reader Challenge.
Learn more at "Transferring SQL Server Login Security" and "Microsoft Articles Cover Transferring Logins with DTS and a Stored Procedure Bug."
Problem
Elaine manages a number of SQL Server machines that are located at several different sites. Most of the servers are running SQL Server 7.0, although the company also has begun deploying SQL Server 2000. Sometimes, Elaine needs to transfer logins in SQL Server from one server to another. Ideally, she wants the transferred logins to keep the same SID for SQL Server logins; this would minimize problems when she uses backup and restore or sp_detach_db and sp_attach_db to copy or move databases. She also wants the SQL Server logins to keep their existing passwords.
Elaine noticed that SQL Server 2000 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.
Help Elaine transfer logins between her SQL Server machines. Elaine would prefer a method that doesn't require her to directly modify any system tables.
Solution
Elaine needs to generate calls to sp_grantlogin for each Windows NT account and sp_addlogin for each SQL Server account. For SQL Server accounts, she needs to supply the password and the SID. One way to perform these tasks is to generate the stored procedure execution call by reading data from the current system table. Elaine could then save the query result as a script file, as the following simplified example code shows:
SELECT 'EXEC sp_addlogin ' + name + ', ' + password FROM syslogins
However, this technique doesn't work well for syslogins because syslogins contains binary and Unicode data, which don't concatenate easily into a string. The trick to correctly generating the binary and Unicode parameters is to separate the parameters into their own columns in the result set. That way, Elaine wouldn't have to concatenate the values together with a string, and binary data would be returned in the format that the stored procedure expects to receive it. 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.
Elaine 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
She could then save the output as a file and execute that file in the destination server. She would get an error message if a login already exists. If Elaine didn't want to get the error messages, she 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.
Another option is for Elaine to create a table to hold the values that she needs to create a login. She could insert data into that table (based on a SELECT against syslogins) and do a native bulk copy into the destination server, using the same table structure. She could then use a cursor to loop through the table and execute sp_addlogin or sp_grantlogin for each row, depending on whether she's working with a SQL Server login or an NT login.