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

From: Pete F (phf175SPAMBLOCK@hotmail.com)
Date: 12/30/02 

From: "Pete F" <phf175SPAMBLOCK@hotmail.com>
Date: Mon, 30 Dec 2002 22:11:34 +0100

I'm not sure if I understand you - are you saying that skip_encryption_old
is _not_supported, despite being documented in BOL?

I understand the emphasis on using the Upgrade Wizard, but surely if
something is documented, then it's reasonable to assume that you (MS) will
support any code using that syntax/feature/functionality?

If I did understand you correctly, and skip_encryption_old isn't supported,
then where is it documented that only the Upgrade Wizard is a supported
upgrade path from 6.5? I couldn't find anything in an admittedly cursory
glance through BOL.

Thanks,

Pete

"Richard Waymire [MS]" <rwaymi@online.microsoft.com> wrote in message
news:O#UQS53rCHA.2492@TK2MSFTNGP10...
> 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: Transferring logins form 6.5 to 2000, anyone?!
    ... Hi Pete, ... the best tested & fully supported mechanism is the upgrade wizard. ... | Subject: Re: Transferring logins form 6.5 to 2000, ... |> support any code using that syntax/feature/functionality? ...
    (microsoft.public.sqlserver.security)
  • Re: When will MS Stop supporting VB?
    ... Paul Clement wrote: ... > As opposed to suggesting they use a version of the product that is at a dead end with respect to new ... > features and on it's last legs with respect to support? ... The Upgrade Wizard is not what makes upgrading difficult. ...
    (microsoft.public.vb.general.discussion)
  • Re: When will MS Stop supporting VB?
    ... > As opposed to suggesting they use a version of the product that is at a dead end with respect to new ... > features and on it's last legs with respect to support? ... Couple that with a worthless "Upgrade Wizard" and it spells death to the ...
    (microsoft.public.vb.general.discussion)
  • Re: SQL Server regular expressions
    ... BOL under the title "Pattern Matching in Search Conditions")... ... more advanced regular expression support, you can try the extended stored ...
    (microsoft.public.sqlserver.programming)
  • Problems with BOL
    ... clicking on embedded links: ... Object doesn't support this property or method ... then have to search BOL for the specific item and reach it that way. ...
    (microsoft.public.sqlserver.tools)