Re: Transferring logins form 6.5 to 2000, anyone?!
From: Pete F (phf175SPAMBLOCK@hotmail.com)
Date: 12/21/02
- Next message: Umachandar Jayachandran: "Re: Unkown sql standar login pass"
- Previous message: stefan barlow: "Re: SSL over the internet"
- In reply to: Jyothi Pai [MS]: "RE: Transferring logins form 6.5 to 2000, anyone?!"
- Next in thread: Richard Waymire [MS]: "Re: Transferring logins form 6.5 to 2000, anyone?!"
- Reply: Richard Waymire [MS]: "Re: Transferring logins form 6.5 to 2000, anyone?!"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Pete F" <phf175SPAMBLOCK@hotmail.com> Date: Sat, 21 Dec 2002 11:51:10 +0100
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!
> |
>
- Next message: Umachandar Jayachandran: "Re: Unkown sql standar login pass"
- Previous message: stefan barlow: "Re: SSL over the internet"
- In reply to: Jyothi Pai [MS]: "RE: Transferring logins form 6.5 to 2000, anyone?!"
- Next in thread: Richard Waymire [MS]: "Re: Transferring logins form 6.5 to 2000, anyone?!"
- Reply: Richard Waymire [MS]: "Re: Transferring logins form 6.5 to 2000, anyone?!"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
