SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_help_revlogin


sp_help_revlogin

Author
Message
twm
twm
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
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 Unsure



Ewan Hampson
Ewan Hampson
Old Hand
Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)

Group: General Forum Members
Points: 306 Visits: 1826
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


twm
twm
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
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...



Ewan Hampson
Ewan Hampson
Old Hand
Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)

Group: General Forum Members
Points: 306 Visits: 1826
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
Kyle Neier ,
Kyle Neier ,
SSC Veteran
SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)

Group: General Forum Members
Points: 270 Visits: 1188
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
Ewan Hampson
Ewan Hampson
Old Hand
Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)

Group: General Forum Members
Points: 306 Visits: 1826
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search