Re: Transferring logins form 6.5 to 2000, anyone?!

From: Richard Waymire [MS] (rwaymi@online.microsoft.com)
Date: 12/28/02 

From: "Richard Waymire [MS]" <rwaymi@online.microsoft.com>
Date: Sat, 28 Dec 2002 09:07:54 -0800

Note the difference - this approach could work but if it doesn't it's not
supported. 

--
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pete F" <phf175SPAMBLOCK@hotmail.com> wrote in message
news:3e044820_4@news.bluewin.ch...
> In fact it is possible to transfer 6.5 logins to 2000 including the
> password - see the code below which uses the skip_encryption_old option of
> sp_addlogin. Since it's documented in BOL, I guess this means it's
supported
> by MS, or should be. The only issue with this approach is that if the 6.5
> server is case-insenstive, and the 2000 server is case-sensitive, then you
> end up with all your passwords in upper case when they're copied. Apart
from
> that, it worked fine for me, although there may easily be something in
your
> environment which wasn't in mine...
>
> Pete
>
> CREATE  proc dbo.TransferSQL65Logins
>     @SourceServer sysname   -- SQL6.5 server to transfer logins from
> as
> set nocount on
> begin
>
> /* Declare variables */
>
> declare @sql varchar(2000),   -- string for dynamic SQL execution
>  @65Login sysname,   -- login on the 6.5 server
>  @65Password sysname,   -- password on the 6.5 server
>  @DefaultDB sysname   -- default database for the login
>
> /* Create temporary worktable */
>
> create table #t (
>  OldLogin sysname not null,
>  OldPassword sysname null,  -- allow for blank passwords on the source
> server
>  DefaultDB sysname not null
> )
>
> /* Get 6.5 login information into the worktable, excluding system logins.
*/
>
> set @sql = 'insert into #t
>      select  name,
>        password,
>        dbname
>      from ' +
>    @SourceServer + '.master.dbo.syslogins
>          where
>    name not in (''sa'', ''probe'', ''repl_publisher'',
''repl_subscriber'')'
>
> --select @sql -- uncomment this for debugging
> exec (@sql)
>
> /* There may be quotes in the encrypted password string, so
> ** double them to escape them. Otherwise the dynamic SQL will
> ** fail.
> */
>
> update #t set OldPassword = replace(OldPassword, '''', '''''')
>
> /* For each login, execute sp_addlogin with skip_encryption_old */
>
> while exists (select OldLogin from #t)
>  begin
>   select @65Login = min(OldLogin) from #t
>   /* Make sure we process NULL passwords properly */
>   select @65Password = coalesce(OldPassword, '') from #t where OldLogin =
> @65Login
>   select @DefaultDB = DefaultDB from #t where OldLogin = @65Login
>
>   set @sql = 'exec sp_addlogin @loginame=''' + @65Login +
>       ''', @passwd=''' + @65Password +
>       ''', @defdb=''' + @DefaultDB +
>       ''', @encryptopt = ''skip_encryption_old''' -- see BOL sp_addlogin
>
>   --select @sql -- uncomment this for debugging
>   exec (@sql)
>
>   delete from #t where OldLogin = @65Login
>  end -- of while loop
>
> end -- of proc
>
>
> "Jyothi Pai [MS]" <jyothip@online.microsoft.com> wrote in message
> news:njh5GDwpCHA.2152@cpmsftngxa09...
> > Hi Hans,
> >
> > The *only* supported way to transfer all the logins along with their
> > password from SQL Server 6.5 to SQL Server 2000 is to upgrade the
> databases
> > using the version upgrade wizard. If you want to transfer only logins
with
> > the password, there is a way to do this using DTS in SQL Server 2000.
> > However, this is only possible between two SQL Server 2000 servers or
> > between two 7.0 servers or from 7.0 to 2000 only. The procedure is
> outlined
> > in the following article:
> >
> > INF: How To Transfer Logins and Passwords Between SQL Servers
> > http://support.microsoft.com/?kbid=246133
> >
> > However, transferring the logins with the password from 6.5 to 2000 is
not
> > possible (as far as my knowledge goes) using any other methods.
Scripting
> > the logins out of the 6.5 Server and running the script on the 2000
server
> > would be the easiest solution to move the logins although the password
is
> > re-set to NULL.
> >
> > Regards,
> > Jyothi Pai
> > Microsoft Online Support Engineer
> >
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > --------------------
> > | From:     Hans Brouwer <hansbrouwer@hotmail.com>
> > | X-Newsreader: AspNNTP 1.50 (ActionJackson.com)
> > | Subject: Transferring logins form 6.5 to 2000, anyone?!
> > | Mime-Version: 1.0
> > | Content-Type: text/plain; charset="us-ascii"
> > | Content-Transfer-Encoding: 7bit
> > | Message-ID: <enmqFUmpCHA.2064@TK2MSFTNGP12>
> > | Newsgroups: microsoft.public.sqlServer.security
> > | Date: Tue, 17 Dec 2002 23:13:51 -0800
> > | NNTP-Posting-Host: actionjackson37.dsl.frii.net 216.17.146.37
> > | Lines: 1
> > | Path: cpmsftngxa09!TK2MSFTNGP08!TK2MSFTNGP12
> > | Xref: cpmsftngxa09 microsoft.public.sqlserver.security:9831
> > | X-Tomcat-NG: microsoft.public.sqlserver.security
> > |
> > | Hi there,
> > |
> > | I need to transfer several hundred logins from SQL Server 6.5 to 2000,
> > | and this several times on different servers. It's only the logins,
their
> > | database rights and passwords, NOT any database. As far as I can see I
> > | cannot use the upgradewizard for this. I have tried several things,
> > | basically transferring logins + password from the syslogins in v65 to
> > | sysxlogins to v2000. It all stumbles on the password transfer, which
> > | does not happen, probably due to securityconsiderations.
> > |
> > | Tnx for your time,
> > |
> > | Hans Brouwer
> > |
> > | *** Sent via Developersdex http://www.developersdex.com ***
> > | Don't just participate in USENET...get rewarded for it!
> > |
> >
>
>

Relevant Pages

  • Re: [Full-Disclosure] Re: IRCXpro 1.0 - Clear local and default remote admin passwords
    ... In some mail from IRCXpro Support, ... > Reply to Feedback from Darren: ... has never stopped them from supporting encrypted passwords. ... something citrix like where a central server does support multiple ...
    (Full-Disclosure)
  • Re: login password changed
    ... available from Microsoft (reference Knowledge Base Article 246133). ... executed on the target server to create the logins with their original SIDs ... The passwords after transfer ...
    (microsoft.public.sqlserver.server)
  • Log Shipping Passwords
    ... I am trying to transfer my SQL logins from the primary ... server and resolve the passwords on the secondary ... If people change their passwords I want to ...
    (microsoft.public.sqlserver.setup)
  • Re: Log Shipping Passwords
    ... Why not use sp_help_revlogin so you re-create the logins on the other end with same sid and password, ... Tibor Karaszi, SQL Server MVP ... If people change their passwords I want to ... > update the secondary server with the new passwords. ...
    (microsoft.public.sqlserver.setup)
  • Moving logins to new server ???
    ... >backend from an old server to a totally new one. ... >The website database uses SQL authentication to grant ... >There are HUNDREDS of logins. ... >sever, complete with passwords? ...
    (microsoft.public.sqlserver.security)