January 28, 2009 at 1:53 pm
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.
January 28, 2009 at 2:38 pm
I would suggest you read following articles first.
January 28, 2009 at 2:43 pm
-- 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
January 28, 2009 at 3:36 pm
Grasshopper...i have done this
http://support.microsoft.com/kb/918992/
SP_HELP_REVLOGIN and also ran the fix orphans too.
January 29, 2009 at 7:23 am
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
January 29, 2009 at 11:04 am
Can you use the new login to log on the B server, or you just can't use the new login to access some databases?
Please post the error messages you got. Other people may help with more information.
January 29, 2009 at 1:36 pm
I have not tried the link method.
The only message i get is invalid pwd.
I have to delete the logins ...
Is there a way to delete all the logins where login user is mapped to this a particular DATABASE
rather than delete all logins.
Cheers
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply