Ramblings of a DBA

How to transfer SQL logins between SQL Server 2005 instances

There are many ways to transfer SQL logins between SQL Server instances.  Here are a few of them:

1. sp_help_revlogin
2. Transfer Logins Task in a DTS package
3. Transfer Logins Task in an SSIS package
4. SQL Server Magazine method
5. Manually (like that's even a consideration for an experienced DBA)

In an ideal world, an application will connect to a SQL Server instance using one account (or perhaps a couple extra) and then "authenticate" individual users via application code and a SQL table. 

We have a legacy system that has thousands of SQL accounts, one per user.  We've got over 8,000 users now.  Twice a year, we move all of our production applications and databases to our disaster recovery site to either test our DR readiness or so that we can perform wide scale maintenance at our primary site.  Due to this, we must synchronize the users between the sites for this one particular system.  Luckily, our other systems live in an ideal world.

All of the transfer logins methods listed above have issues (at least for us, your mileage may vary), so I decided to write my own. 

SET NOCOUNT ON

CREATE TABLE #Logins
(
    loginId int IDENTITY(1, 1) NOT NULL,
    loginName nvarchar(128) NOT NULL,
    passwordHash varbinary(256) NULL,
    sid varbinary(85) NOT NULL
)

-- openquery is used so that loginproperty function runs on the remote server,
-- otherwise we get back null
INSERT INTO #Logins(loginName, passwordHash, sid)
SELECT *
FROM OPENQUERY([LinkedServerName], '
SELECT name, CONVERT(varbinary(256), LOGINPROPERTY(name, ''PasswordHash'')), sid
FROM master.sys.server_principals
WHERE
    type = ''S'' AND 
    name NOT IN (''sa'', ''guest'') AND 
    create_date >= ''12/31/2005''
ORDER BY name')

DECLARE 
    @count int, @loginId int, @loginName nvarchar(128), 
    @passwordHashOld varbinary(256), @passwordHashNew varbinary(256), 
    @sid varbinary(85), @sql nvarchar(4000), @password varchar(514)

SELECT @loginId = 1, @count = COUNT(*)
FROM #Logins

WHILE @loginId <= @count
BEGIN
    SELECT @loginName = loginName, @passwordHashNew = passwordHash, @sid = sid
    FROM #Logins
    WHERE loginId = @loginId

    -- if the account doesn't exist, then we need to create it
    IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE name = @loginName)
    BEGIN
        EXEC master.dbo.sp_hexadecimal @passwordHashNew, @password OUTPUT

        SET @sql = 'CREATE LOGIN ' + @loginName + ' WITH PASSWORD = '
        SET @sql = @sql + CONVERT(nvarchar(512), COALESCE(@password, 'NULL')) 
        SET @sql = @sql + ' HASHED, CHECK_POLICY = OFF' 
        --PRINT @sql
        EXEC (@sql)

        --PRINT 'login created'
    END
    -- if the account does exist, then we need to drop/create to sync the password;
    -- can't alter as hashed isn't supported
    ELSE
    BEGIN
        SELECT @passwordHashOld = CONVERT(varbinary(256), LOGINPROPERTY(@loginName, 'PasswordHash'))

        -- only bother updating if the password has changed since the last sync
        IF @passwordHashOld <> @passwordHashNew
        BEGIN
            EXEC master.dbo.sp_hexadecimal @passwordHashOld, @password OUTPUT

            SET @sql = 'DROP LOGIN ' + @loginName
            --PRINT @sql
            EXEC (@sql)

            SET @sql = 'CREATE LOGIN ' + @loginName + ' WITH PASSWORD = '
            SET @sql = @sql + CONVERT(nvarchar(512), COALESCE(@password, 'NULL'))
            SET @sql = @sql + ' HASHED, CHECK_POLICY = OFF' 
            --PRINT @sql
            EXEC (@sql)

            --PRINT 'login "altered"'
        END
    END

    SET @loginId = @loginId + 1
END

DROP TABLE #Logins

The script depends on sp_hexadecimal.  You can get it here.

To transfer SQL logins between SQL Server 2005 instances using my script, run it on the server that will receive the updates.  "LinkedServerName" in the OPENQUERY statement needs to be changed to the linked server that points to the source server.

Print | posted on Tuesday, June 24, 2008 9:16 AM |