SQL Logins

Question

Post reply

SQL Logins

TRACEY-320982

SSChampion

Points: 13653
More actions
January 28, 2009 at 1:53 pm

#129842

hi i have SQL SERVER 2005 A and SQL SERVER 2005 B

No logins in SQL SERVER 2005 B so i used the

sp_help_revlogin to move all the logins over.

However it looks like i have to reset everyones password as this is not working on the SQL SERVER 2005 B server.

-- Login: TRawlings

CREATE LOGIN [TRawlings] WITH PASSWORD = xxxxlongname here, SID = 0x8long codehere, CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Any idea how to use this to keep the passwords identicial.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

richardq.s SSC Enthusiast Points: 198 More actions · Answer 1

richardq.s

SSC Enthusiast

Points: 198

January 28, 2009 at 2:38 pm

#934223

I would suggest you read following articles first.

http://support.microsoft.com/kb/246133

http://support.microsoft.com/kb/274188/

fhanlon SSChampion Points: 13131 More actions · Answer 2

-- Setup a linked server on Server B called ServerA from which the

-- standard logins needs to be transferred. You can call it

-- whatever you want & modify the linked server name also.

declare @login sysname , @password sysname

declare implogins cursor for

select name , password

from ServerA.master.dbo.syslogins

where isntname = 0 and charindex( 'repl_' , name ) = 0 and

charindex( 'distributor' , name ) = 0 and name <> 'sa'

open implogins

while ( 'FETCH IS OK' = 'FETCH IS OK' )

begin

fetch implogins into @login , @password

if @@fetch_status < 0 break

exec sp_addlogin @login , @password , @encryptopt = 'skip_encryption'

end

deallocate implogins

go

Francis

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 3

TRACEY-320982

SSChampion

Points: 13653

January 28, 2009 at 3:36 pm

#934280

Grasshopper...i have done this

http://support.microsoft.com/kb/918992/

SP_HELP_REVLOGIN and also ran the fix orphans too.

fhanlon SSChampion Points: 13131 More actions · Answer 4

Are you saying its working now or not working? The reference you gave as well as you first post used CREATE LOGIN. In the code I gave in may last post I did not use this. Did you try my code? You could modify my code to transfer only one id where you know the password and then test it. I just tried it and it works fine. I've used this to transfer ids many times.

Francis