February 17, 2004 at 4:42 am
I'm trying to move a login from one server to another... I have copied and modified T-SQL code found on this site and executed (fixingbrokenlogins). The login creates ok on other server but it gives invalid login error. Any ideas what's wrong with the syntax or why it doesn't set the password??
SQL statement:
SELECT 'sp_addlogin @loginame = ' + a.name + ',
@passwd = "N' + a.password + '",
@encryptopt = skip_encryption' + char(13) + 'GO'
FROM syslogins AS a INNER JOIN maxprod.dbo.sysusers AS p
ON a.name = p.name
where a.name = 'RUSSELLVILLE'
Creates:
sp_addlogin @loginame = RUSSELLVILLE,
@passwd = "N??????????????????????",
@encryptopt = skip_encryption
GO
Execution adds the user!
If I try to login with the user name and password to SQL Analyzer, I get this message: Login fails for user!! Same error if I try to login to the application!!
Your input is appreciated,
Curtis
February 17, 2004 at 6:27 am
Try this
select 'sp_addlogin ''' + a.name + ''',@sid=',a.sid,',@passwd=',
CONVERT (VARBINARY (32), a.password),',@encryptopt=''skip_encryption''' + char(13) + 'GO'
FROM syslogins AS a
INNER JOIN maxprod.dbo.sysusers AS p
ON a.name = p.name
where a.name = 'RUSSELLVILLE'
Which will convert the password to varbinary plus will also set the sid. This works OK for me on SQL7.
Far away is close at hand in the images of elsewhere.
Anon.
February 17, 2004 at 11:35 am
The conversion to carbinary is important. The problem isn't a syntax one so much as you are cutting and pasting the output then reexecuting it. Passsords (and sometime other system data) doesn't like to be converted to/from character which is what cut/paste does. Try saving the name and password into variables:
eg. declare @login sysname , @password sysname
SELECT @login = a.name , @password= a.password .....
exec sp_addlogin @login , @password , @encryptopt = 'skip_encryption'
Francis
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply