Moving logins from one server to another

Historically, many of us have used bulk copy to move selected contents of syslogins from one server to another so that the logins stayed in sync for things such as replication, log shipping, etc.

In SQL 2005, I have no access to passwords (this is proper from a security perspective) so I can't generate a file with bulk copy. I also can't update sys.server_principals, so it doesn't really matter that I can't export the passwords.

So what is the SQL 2005 solution? Suppose I decide to mirror my database. How do I get the logins that are users of that database over to the mirror server? Scripting the logins won't work because it generates a random password. sp_change_users_login requires me to provide a password but then I would have to know each login's current password or give them each a new password. This is viable with 2 users; it is not viable with 2000!

I know that Copy Database Wizard will move the logins with the passwords intact, but it will assign new SIDs to SQL Server authentication logins. That's fine as long as it is a one-time move but not if I am maintaining a warm standby.

This problem has been one of the most frequent problems posted on newsgroups, etc. in the past. But we have had solutions. I fear that we do not have one in SQL 2005.

I'd appreciate any help you can provide.

Thanks,

Sharon

If you are a sysadmin, you can get the password hash from the password column in sys.syslogins. Then you can script the login with:

CREATE LOGIN <login_name> WITH PASSWORD = <password_hash> HASHED

There is a KB article for SQL 2000: http://support.microsoft.com/default.aspx/kb/246133/. We are in the process of updating the procedure in this article so that it can be used with SQL Server 2005.

A reader of this forum has also posted his own rewrite of the KB article procedure: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=309521&SiteID=1. Note that it comes with no guarantees from anyone. If you have any comments about this, please post in that thread.

Note that you can also get the password hash using the loginproperty builtin that I described at: http://blogs.msdn.com/lcris/archive/2005/12/01/499095.aspx.

Thanks
Laurentiu

|||

This is an updated version of sp_HelpRevlogin. I only do it for logins with access to the server, i.e. HasAccess = 1

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

Create PROCEDURE [dbo].[sp_Help_RevLogin2005]

As

DECLARE @.name sysname

DECLARE @.IsNTName int

DECLARE @.denyLogin int

DECLARE @.binpwd varbinary(256)

DECLARE @.txtpwd nvarchar(128)

DECLARE @.tmpstr nvarchar (256)

DECLARE @.SID_varbinary varbinary(85)

DECLARE @.SID_string nvarchar(128)

DECLARE login_curs CURSOR FOR

SELECT sid, name, isNTName, denyLogin, convert(varbinary(256),password) as password FROM master..syslogins

WHERE name <> 'sa' and hasAccess = 1

OPEN login_curs

FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.IsNTName, @.DenyLogin, @.binpwd

IF (@.@.fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @.tmpstr = '/* sp_help_revlogin script '

PRINT @.tmpstr

SET @.tmpstr = '** Generated '

+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'

PRINT @.tmpstr

PRINT ''

PRINT 'DECLARE @.pwd sysname'

WHILE (@.@.fetch_status <> -1)

BEGIN

IF (@.@.fetch_status <> -2)

BEGIN

PRINT ''

SET @.tmpstr = '-- Login: ' + @.name

PRINT @.tmpstr

IF @.IsNTName = 1

BEGIN -- NT authenticated account/group

IF @.denyLogin = 1

BEGIN -- NT login is denied access

SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''

PRINT @.tmpstr

END

ELSE BEGIN -- NT login has access

SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''

PRINT @.tmpstr

END

END

ELSE BEGIN -- SQL Server authentication

IF (@.binpwd IS NOT NULL)

BEGIN -- Non-null password

EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT

SET @.tmpstr = 'SET @.pwd = ' + @.txtPwd

PRINT @.tmpstr

EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT

SET @.tmpstr = 'Create Login ' + @.name + ' WITH PASSWORD=' + @.txtpwd + ' HASHED, SID=' + @.sid_string

PRINT @.tmpstr

END

ELSE BEGIN

-- Null password

EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT

SET @.tmpstr = 'Create Login ' + @.name + ' WITH SID=' + @.sid_string

END

END

END

FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.IsNTName, @.DenyLogin, @.binpwd

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0