Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_help_revlogin Expand / Collapse
Author
Message
Posted Monday, August 3, 2009 10:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 13, 2011 7:10 PM
Points: 27, Visits: 349
Hi,

I have 2 sql server logins in S2K that I need to transfer across to S2K5.

When I run the above SP, I get the following:

-- login: xxx1
exec master..sp_addlogin 'xxx1', null, @sid = 0x937210etc, @encryptopt = 'skip_encryption'

-- login: xxx2
set @pwd = convert (varbinary(256), 0x01008E3D1C57C3etc)
exec master..sp_addlogin 'xxx2', @pwd, @sid = 0x6546CFDetc, @encryptopt = 'skip_encryption'

The second login works fine, but the first login doesn't work and seems to be missing the "set @pwd".

Any ideas?

thanks



Post #764574
Posted Thursday, August 6, 2009 2:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 142, Visits: 1,729
Null password? sp_help_revlogin includes
    ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END

Post #766020
Posted Wednesday, August 12, 2009 8:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 13, 2011 7:10 PM
Points: 27, Visits: 349
yes....but I am wondering why it generates a command to run on SQL Server 2005 that has a null password but the command won't accept the null password...


Post #769802
Posted Thursday, August 13, 2009 2:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 142, Visits: 1,729
I haven't done a migration from 2K to 2K5, so I'm speculating: perhaps the 2K5 server security policy will not accept a NULL password.

Just to get the accounts transferred, you could add a SET line with a generic password, and replace the NULL in the sp_addlogin line. At least all would then have passwords.

Or, since sp_addlogin is on the way out after SQL2K5, you could generate CREATE LOGIN statements instead, and use the CHECK_POLICY option to turn off security settings. But that would leave you with those NULL passwords, and part of the exercise ought to be to eradicate them. If your users are unwilling, you can always sell it as improved security from the new version

Edit - I say "all", but I see it is only 2 users; still, the principle applies
Post #769972
Posted Thursday, August 13, 2009 9:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:34 AM
Points: 194, Visits: 1,154
There is a revlogin sp specifically for migrations from 2000 to 2005.

Look at sp_help_revlogin_2000_to_2005 in this document:
http://support.microsoft.com/kb/246133

Not certain it will fix your problem, but definitely worth a try before debugging their sp.

Kyle
Post #770241
Posted Thursday, August 13, 2009 9:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 142, Visits: 1,729
Nice catch - I should have checked: it uses CREATE LOGIN and turns off checking for null passwords. Though I would still rather correct that situation than leave it.
Post #770278
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse