Ramblings of a DBA

Tara Kizer
posts - 166, comments - 835, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

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 |

Feedback

Gravatar

# re: How to transfer SQL logins between SQL Server 2005 instances

I tested your solution and it appears it only works for FULL Alpha user IDs. We use XXXX-NAME as out logins (For programattic reasons) and I get the following:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1000'. <------ This is the first portion of the user name 1000-admin
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1000'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1023'.

8/19/2008 8:23 AM | Neil
Gravatar

# re: How to transfer SQL logins between SQL Server 2005 instances

In finally got the create user portions working but SQL does not like:
CONVERT(nvarchar(512), COALESCE(@password, 'NULL'))

I keep getting an error "Error Near NULL" when the create user statement is run.
8/19/2008 9:41 AM | Neil
Gravatar

# re: How to transfer SQL logins between SQL Server 2005 instances

We just started with SQL 2005 mirroring and when searching for a nice way of copying users across the sql servers we found this solution. It works great, except that we just found out that the SID's are different between the 2 server when we tried testing the failover.

By looking at the script it seems as if you used the original SID's from the principal server when creating them on the mirror, would there be any explanation why we end up with different SID's between the two servers after using the script?
8/27/2008 5:34 AM | Jeroen
Gravatar

# re: How to transfer SQL logins between SQL Server 2005 instances

Found a solution that seems to work for us

Changed 2 lines in the script

Old: SET @sql = @sql + ' HASHED, CHECK_POLICY = OFF'



New: SET @sql = @sql + ' HASHED, CHECK_POLICY = OFF'
+ ',SID = ' + master.sys.fn_varbintohexstr(@SID)


This way it uses the original SID for creating the user, however it made me wondering why you didn't include the SID in the create statement but did retrieve it with the select option?
8/27/2008 6:04 AM | Jeroen
Gravatar

# re: How to transfer SQL logins between SQL Server 2005 instances

Jeroen,

Thank you for the follow-up. I'm sure I had a plan for the sid variable. ;-) I don't recall though as I wrote the code several months ago.
8/27/2008 2:09 PM | Tara
Gravatar

# re: How to transfer SQL logins between SQL Server 2005 instances

Walked into a new problem with the script.

A hopefully usefull tip: We are using SQL mirroring and everytime you synchronize the users it will remove your endpoint mapping on the server and disconnects the login from the user in the master database.

To solve it simply add your users used by mirroring for server A, B and witness to the script like the exclusion of SA and Guest

9/22/2008 12:54 AM | Jeroen
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET