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!
> > |
> >
>
>