Difference between revisions of "Migrate master database from ASE 12.5 to 15.0.2"

From SybaseWiki
Jump to: navigation, search
m
 
Line 1: Line 1:
 
Migration of the master database from a 12.5 server into a 15.0.2 server can be done by extracting the required information from the old server and inserting it into the new 15.0.2 server.  
 
Migration of the master database from a 12.5 server into a 15.0.2 server can be done by extracting the required information from the old server and inserting it into the new 15.0.2 server.  
  
Below you will find a script that does this for you.
+
The script below will reverse-engineer the logins, passwords and roles on the existing 12.5 server and create a sql script. The sql script will then be applied at the 15.0.2 server.
  
 
  #!/bin/sh
 
  #!/bin/sh
 
   
 
   
  if [ $# != 6 ]
+
  if [ $# != 7 ]
 
  then
 
  then
   echo "Usage: $0 <source> <source-login> <source-passwrd> <target> <target-login> <target-password>"
+
   echo "Usage: $0 <source> <source-login> <source-passwrd> <target> <target-login> <target-password> <interfaces file>"
 
   exit 1
 
   exit 1
 
  fi
 
  fi
Line 17: Line 17:
 
  TARGET_USER="$5"
 
  TARGET_USER="$5"
 
  TARGET_PWD="$6"
 
  TARGET_PWD="$6"
 +
INTERFACES="$7"
 
   
 
   
  isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} <<EOF
+
echo    "** Testing connectivity to servers"
use tempdb
+
go
+
  isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -I ${INTERFACES} <<EOF > /tmp/$$.check
if exists(
+
select 1 from sysobjects where name = "my_syslogins")
+
drop view my_syslogins
+
go
+
create view my_syslogins
+
as
+
select *,null as lastlogindate,getdate() as crdate,null as locksuid,null as lockreason,null as lockdate
+
from master..syslogins where suid > 2
+
go
+
if exists(
+
select 1 from sysobjects where name = "my_remotelogins")
+
drop view my_remotelogins
+
go
+
create view my_remotelogins
+
as
+
select * from master..sysattributes where object_type = "EL"
+
go
+
if exists(
+
select 1 from sysobjects where name = "my_userroles")
+
drop view my_userroles
+
go
+
create view my_userroles
+
as
+
select * from master..sysattributes where object_type = "UR"
+
go
+
if exists(
+
select 1 from sysobjects where name = "my_sysloginroles")
+
drop view my_sysloginroles
+
go
+
create view my_sysloginroles
+
as
+
select * from master..sysloginroles where suid > 2
+
go
+
if exists(
+
select 1 from sysobjects where name = "my_sysroles")
+
drop view my_sysroles
+
go
+
create view my_sysroles
+
as
+
select * from master..sysroles where id >= 32
+
go
+
if exists(
+
select 1 from sysobjects where name = "my_sysservers")
+
drop view my_sysservers
+
go
+
create view my_sysservers
+
as
+
select *,null as srvstatus2
+
from master..sysservers
+
where srvname != "SYB_BACKUP"
+
and srvname not like "%_migrate"
+
and srvclass != 0 -- local server
+
go
+
if exists(
+
select 1 from sysobjects where name = "my_syssrvroles")
+
drop view my_syssrvroles
+
go
+
create view my_syssrvroles
+
as
+
select * from master..syssrvroles where srid >= 32
+
go
+
if exists(
+
select 1 from sysobjects where name = "my_sysusers_1")
+
drop view my_sysusers_1
+
go
+
create view my_sysusers_1
+
as
+
select *
+
from master..sysusers su
+
where su.suid > 2
+
and exists(
+
select 1
+
from master..syslogins sl
+
where sl.suid = su.suid)
+
go
+
if exists(
+
select 1 from sysobjects where name = "my_sysusers_2")
+
drop view my_sysusers_2
+
go
+
create view my_sysusers_2
+
as
+
select su.*
+
from master..sysusers su,
+
master..sysroles sr
+
where su.uid = sr.lrid
+
and sr.id >= 32
+
go
+
 
  EOF
 
  EOF
 
   
 
   
  echo "Press <ENTER> to continue" ; read OK
+
  COUNT=`grep CT-LIBRARY /tmp/$$.check | wc -l`
 +
if [ ${COUNT} -gt 0 ]
 +
then
 +
        cat /tmp/$$.check
 +
        rm -f /tmp/$$.*
 
   
 
   
bcp tempdb..my_syslogins out syslogins.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -c
+
        echo "** Could not connect to Source server"
bcp master..sysalternates out sysalternates.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n
+
bcp master..sysremotelogins out sysremotelogins.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n
+
bcp tempdb..my_remotelogins out remotelogins.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -c
+
bcp tempdb..my_userroles out userroles.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -c
+
bcp tempdb..my_sysloginroles out sysloginroles.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n
+
bcp tempdb..my_sysroles out sysroles.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n
+
bcp tempdb..my_sysservers out sysservers.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n
+
bcp tempdb..my_syssrvroles out syssrvroles.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n
+
bcp tempdb..my_sysusers_1 out sysusers_1.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n
+
bcp tempdb..my_sysusers_2 out sysusers_2.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n
+
 
   
 
   
  echo "Press <ENTER> to continue" ; read OK
+
        exit 1
 +
  fi
 
   
 
   
  isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} <<EOF
+
  isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} <<EOF > /tmp/$$.check
  sp_configure "allow updates to system tables",1
+
  EOF
go
+
 
   
 
   
  delete master..syslogins
+
  COUNT=`grep CT-LIBRARY /tmp/$$.check | wc -l`
where suid > 2
+
if [ ${COUNT} -gt 0 ]
 +
then
 +
        cat /tmp/$$.check
 +
        rm -f /tmp/$$.*
 +
 +
        echo "** Could not connect to Target server"
 +
 +
        exit 1
 +
fi
 +
 +
rm -f /tmp/$$.check
 +
 +
echo    "** Connectivity ok"
 +
 +
isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -I ${INTERFACES} -w800 -b <<EOF > logins.sql
 +
set    nocount on
 
  go
 
  go
  delete master..sysalternates
+
  declare lees cursor for
 +
        select  suid,name,dbname,password,language,fullname
 +
                from    master..syslogins
 +
                where  suid    > 2
 +
                order  by suid
 
  go
 
  go
  delete master..sysremotelogins
+
   
 +
declare @suid          int,
 +
        @name          varchar(30),
 +
        @dbname        varchar(30),
 +
        @password      varbinary(30),
 +
        @language      varchar(30),
 +
        @fullname      varchar(30),
 +
        @value          int
 +
 +
open    lees
 +
 +
fetch  lees into
 +
        @suid, @name, @dbname, @password, @language, @fullname
 +
 +
while  @@sqlstatus = 0
 +
begin
 +
        print  "exec sp_addlogin '%1!','migrate123',null,null,'%2!'",
 +
                @name, @fullname
 +
 +
        print  "update syslogins set suid = %1! where name = '%2!'",
 +
                @suid, @name
 +
 +
        if      @dbname != "master"
 +
                print  "update syslogins set dbname = '%1!' where name = '%2!'",
 +
                        @dbname, @name
 +
 +
        print  "update syslogins set password = %1! where name = '%2!'",
 +
                @password, @name
 +
 +
        if      exists(
 +
                select  1
 +
                        from    master..sysattributes
 +
                        where  object          = @suid
 +
                        and    object_type    = "PS"
 +
                        and    attribute      = 0
 +
                        and    object_cinfo    = "login"
 +
                        and    class          = 14)
 +
        begin
 +
                select  @value  = int_value
 +
                        from    master..sysattributes
 +
                        where  object          = @suid
 +
                        and    object_type    = "PS"
 +
                        and    attribute      = 0
 +
                        and    object_cinfo    = "login"
 +
                        and    class          = 14
 +
 +
                print  "exec sp_modifylogin '%1!','passwd expiration','%2!'",
 +
                        @name, @value
 +
        end
 +
 +
        if      exists(
 +
                select  1
 +
                        from    master..sysattributes
 +
                        where  object          = @suid
 +
                        and    object_type    = "PS"
 +
                        and    attribute      = 1
 +
                        and    object_cinfo    = "login"
 +
                        and    class          = 14)
 +
        begin
 +
                select  @value  = int_value
 +
                        from    master..sysattributes
 +
                        where  object          = @suid
 +
                        and    object_type    = "PS"
 +
                        and    attribute      = 1
 +
                        and    object_cinfo    = "login"
 +
                        and    class          = 14
 +
 +
                print  "exec sp_modifylogin '%1!','min passwd length','%2!'",
 +
                        @name, @value
 +
        end
 +
 +
        if      exists(
 +
                select  1
 +
                        from    master..sysattributes
 +
                        where  object          = @suid
 +
                        and    object_type    = "PS"
 +
                        and    attribute      = 2
 +
                        and    object_cinfo    = "login"
 +
                        and    class          = 14)
 +
        begin
 +
                select  @value  = int_value
 +
                        from    master..sysattributes
 +
                        where  object          = @suid
 +
                        and    object_type    = "PS"
 +
                        and    attribute      = 2
 +
                        and    object_cinfo    = "login"
 +
                        and    class          = 14
 +
 +
                print  "exec sp_modifylogin '%1!','max failed_logins','%2!'",
 +
                        @name, @value
 +
        end
 +
 +
        print  "go"
 +
 +
        fetch  lees into
 +
                @suid, @name, @dbname, @password, @language, @fullname
 +
end
 +
 +
close  lees
 
  go
 
  go
  delete master..sysattributes where object_type in ("EL","UR")
+
   
 +
deallocate cursor lees
 
  go
 
  go
  delete master..sysloginroles where suid > 2
+
   
 +
select  "exec sp_adduser '" + name + "'"
 +
        from    master..sysusers su
 +
        where   su.suid > 2
 +
        and    exists(
 +
                select  1
 +
                from    master..syslogins sl
 +
                where  sl.suid = su.suid)
 
  go
 
  go
  delete master..sysroles where id >= 32
+
   
 +
print  "go"
 
  go
 
  go
  delete master..sysservers
+
  EOF
where srvname != "SYB_BACKUP"
+
and srvname not like "%_migrate"
+
echo    "** Script for logins created"
and srvclass != 0 -- local server
+
 +
isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -I ${INTERFACES} -w800 -b <<EOF > roles.sql
 +
set    nocount on
 
  go
 
  go
  delete master..syssrvroles where srid >= 32
+
  declare lees cursor for
 +
        select  srid, name, password, status
 +
                from    master..syssrvroles
 +
                where   srid   >= 32
 
  go
 
  go
  delete master..sysusers
+
   
from master..sysusers su
+
declare @srid          int,
where su.suid > 2
+
        @name          varchar(30),
and not exists(
+
        @password      varbinary(30),
select 1
+
        @status        smallint,
from master..syslogins sl
+
        @value          int
where sl.suid = su.suid)
+
 +
open    lees
 +
 +
fetch  lees into
 +
        @srid, @name, @password, @status
 +
 +
while  @@sqlstatus = 0
 +
begin
 +
        print  "create role %1!",
 +
                @name
 +
 +
        if      @password      != null
 +
                print  "update syssrvroles set password = %1! where name = '%2!'",
 +
                        @password, @name
 +
 +
        if      @status & 2 = 2
 +
                print  "alter role %1! lock",
 +
                        @name
 +
 +
        if      exists(
 +
                select 1
 +
                        from   master..sysattributes
 +
                        where   object          = @srid
 +
                        and    object_type    = "PS"
 +
                        and    attribute      = 0
 +
                        and    object_cinfo    = "role"
 +
                        and    class          = 14)
 +
        begin
 +
                select  @value  = int_value
 +
                        from    master..sysattributes
 +
                        where  object          = @srid
 +
                        and    object_type    = "PS"
 +
                        and    attribute      = 0
 +
                        and    object_cinfo    = "role"
 +
                        and    class          = 14
 +
 +
                print  "alter role %1! set passwd expiration %2!",
 +
                        @name, @value
 +
        end
 +
 +
        print  "go"
 +
 +
        fetch  lees into
 +
                @srid, @name, @password, @status
 +
end
 +
 +
close  lees
 
  go
 
  go
  delete master..sysusers
+
   
from master..sysusers su,
+
deallocate cursor lees
master..sysroles sr
+
where su.uid = sr.lrid
+
and sr.id >= 32
+
 
  go
 
  go
  if exists (select 1 from tempdb..sysobjects where name = "my_sysusers_2")
+
   
drop table tempdb..my_sysusers_2
+
declare lees cursor for
 +
        select suser_name(suid), role_name(srid), status
 +
                from    master..sysloginroles
 +
                where  suid    > 2
 
  go
 
  go
create table tempdb..my_sysusers_2(
 
suid int not null,
 
uid int not null,
 
gid int not null,
 
name sysname not null,
 
environ varchar(255) null)
 
go
 
EOF
 
 
   
 
   
  echo "Press <ENTER> to continue" ; read OK
+
  declare @suser_name    varchar(30),
 +
        @role_name      varchar(30),
 +
        @status        smallint
 
   
 
   
  bcp master..syslogins in syslogins.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -c
+
  open    lees
bcp master..sysalternates in sysalternates.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n
+
bcp master..sysremotelogins in sysremotelogins.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n
+
bcp master..sysattributes in remotelogins.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -c
+
bcp master..sysattributes in userroles.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -c
+
bcp master..sysloginroles in sysloginroles.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n
+
bcp master..sysroles in sysroles.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n
+
bcp master..sysservers in sysservers.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n
+
bcp master..syssrvroles in syssrvroles.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n
+
bcp master..sysusers in sysusers_1.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n
+
bcp tempdb..my_sysusers_2 in sysusers_2.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n
+
 
   
 
   
  echo "Press <ENTER> to continue" ; read OK
+
  fetch  lees into
 +
        @suser_name, @role_name, @status
 
   
 
   
  isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} <<EOF
+
  while  @@sqlstatus = 0
  use tempdb
+
  begin
go
+
        print  "grant role %1! to %2!",
drop view my_syslogins, my_remotelogins, my_userroles, my_sysloginroles,
+
                @role_name, @suser_name
my_sysroles, my_sysservers, my_syssrvroles, my_sysusers_1,
+
my_sysusers_2
+
go
+
EOF
+
 
   
 
   
echo "Press <ENTER> to continue" ; read OK
+
        if      @status = 1
 +
                print  "exec sp_modifylogin '%1!','add default role','%2!'",
 +
                        @suser_name, @role_name
 
   
 
   
isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} <<EOF
+
        print  "go"
use master
+
go
+
declare @offset int
+
 
   
 
   
if exists(
+
        fetch  lees into
select 1
+
                @suser_name, @role_name, @status
from master..sysusers m,
+
  end
tempdb..my_sysusers_2 t
+
where m.uid = t.uid)
+
select @offset = (select max(uid)
+
from master..sysusers)
+
- (select min(uid)
+
from tempdb..my_sysusers_2)
+
+ 1
+
  else
+
select @offset = 0
+
 
   
 
   
  insert into master..sysusers
+
  close  lees
select suid,
+
uid + @offset,
+
gid + @offset,
+
name,
+
environ
+
from tempdb..my_sysusers_2
+
 
  go
 
  go
  drop table tempdb..my_sysusers_2
+
   
 +
deallocate cursor lees
 
  go
 
  go
  sp_configure "allow updates to system tables",0
+
   
 +
select  "grant role " + role_name(object_info1) + " to " + role_name(object)
 +
        from    sysattributes
 +
        where  object_type    = "UR"
 +
        and    attribute      = 2
 +
        and    object_cinfo    = null
 +
        and    class          = 8
 +
go
 +
 +
print  "go"
 
  go
 
  go
 
  EOF
 
  EOF
 
   
 
   
  echo "Press <ENTER> to continue" ; read OK
+
  echo   "** Script for roles created"
 +
echo    "Press <ENTER> to continue (delete values at target server)" ; read OK
 
   
 
   
  rm -f syslogins.dat sysalternates.dat sysremotelogins.dat remotelogins.dat userroles.dat sysloginroles.dat \
+
  isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} <<EOF
  sysroles.dat sysservers.dat syssrvroles.dat sysusers_1.dat sysusers_2.dat
+
sp_configure "allow updates to system tables",1
 +
  go
 
   
 
   
  isql -U${SOURCE_USER} -b -P"${SOURCE_PWD}" -S${SOURCE} <<EOF > /var/tmp/modifylogin
+
  delete master..syslogins where  suid    > 2
  set nocount on
+
 
  go
 
  go
  select "exec sp_modifylogin '" +
+
  delete  master..sysalternates
rtrim(suser_name(object)) +
+
"','passwd expiration','" +
+
convert(varchar(30),int_value) +
+
"'"
+
from master..sysattributes
+
where object_type = "PS"
+
and attribute = 0
+
and object_cinfo = "login"
+
and class = 14
+
 
  go
 
  go
  select "exec sp_modifylogin '" +
+
  delete  master..sysremotelogins
rtrim(suser_name(object)) +
+
"','min passwd length','" +
+
convert(varchar(30),int_value) +
+
"'"
+
from master..sysattributes
+
where object_type = "PS"
+
and attribute = 1
+
and object_cinfo = "login"
+
and class = 14
+
 
  go
 
  go
  select "exec sp_modifylogin '" +
+
  delete  master..sysattributes where object_type in ("EL","UR")
rtrim(suser_name(object)) +
+
go
"','max failed_logins','" +
+
delete  master..sysloginroles where suid > 2
convert(varchar(30),int_value) +
+
go
"'"
+
delete  master..sysroles where id >= 32
from master..sysattributes
+
go
where object_type = "PS"
+
delete  master..syssrvroles where srid >= 32
and object_cinfo = "login"
+
go
and attribute = 2
+
delete  master..sysusers
and class = 14
+
        from    master..sysusers su
 +
        where  su.suid > 2
 +
        and     not exists(
 +
                select  1
 +
                        from    master..syslogins sl
 +
                        where  sl.suid = su.suid)
 +
go
 +
delete  master..sysusers
 +
        from    master..sysusers su,
 +
                master..sysroles sr
 +
        where  su.uid  = sr.lrid
 +
        and     sr.id  >= 32
 
  go
 
  go
 
  EOF
 
  EOF
 
   
 
   
  echo "go" >> /var/tmp/modifylogin
+
  echo "Press <ENTER> to continue (apply generated sql at target server)" ; read OK
 
   
 
   
  cat /var/tmp/modifylogin
+
  isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} < logins.sql
 +
isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} < roles.sql
 +
 +
isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} <<EOF
 +
sp_configure "allow updates to system tables",0
 +
go
 +
EOF
 
   
 
   
  isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} < /var/tmp/modifylogin
+
  rm -f logins.sql roles.sql
 
   
 
   
  rm /var/tmp/modifylogin
+
  echo "Migration script done."
  
 
[[Category:ASE]]
 
[[Category:ASE]]

Latest revision as of 16:55, 17 September 2008

Migration of the master database from a 12.5 server into a 15.0.2 server can be done by extracting the required information from the old server and inserting it into the new 15.0.2 server.

The script below will reverse-engineer the logins, passwords and roles on the existing 12.5 server and create a sql script. The sql script will then be applied at the 15.0.2 server.

#!/bin/sh

if [ $# != 7 ]
then
  echo "Usage: $0 <source> <source-login> <source-passwrd> <target> <target-login> <target-password> <interfaces file>"
  exit 1
fi

SOURCE="$1"
SOURCE_USER="$2"
SOURCE_PWD="$3"
TARGET="$4"
TARGET_USER="$5"
TARGET_PWD="$6"
INTERFACES="$7"

echo    "** Testing connectivity to servers"

isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -I ${INTERFACES} <<EOF > /tmp/$$.check
EOF

COUNT=`grep CT-LIBRARY /tmp/$$.check | wc -l`
if [ ${COUNT} -gt 0 ]
then
       cat /tmp/$$.check
       rm -f /tmp/$$.*

       echo "** Could not connect to Source server"

       exit 1
fi

isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} <<EOF > /tmp/$$.check
EOF

COUNT=`grep CT-LIBRARY /tmp/$$.check | wc -l`
if [ ${COUNT} -gt 0 ]
then
       cat /tmp/$$.check
       rm -f /tmp/$$.*

       echo "** Could not connect to Target server"

       exit 1
fi

rm -f /tmp/$$.check

echo    "** Connectivity ok"

isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -I ${INTERFACES} -w800 -b <<EOF > logins.sql
set     nocount on
go
declare lees cursor for
        select  suid,name,dbname,password,language,fullname
                from    master..syslogins
                where   suid    > 2
                order   by suid
go

declare @suid           int,
        @name           varchar(30),
        @dbname         varchar(30),
        @password       varbinary(30),
        @language       varchar(30),
        @fullname       varchar(30),
        @value          int

open    lees

fetch   lees into
        @suid, @name, @dbname, @password, @language, @fullname

while   @@sqlstatus = 0
begin
        print   "exec sp_addlogin '%1!','migrate123',null,null,'%2!'",
                @name, @fullname

        print   "update syslogins set suid = %1! where name = '%2!'",
                @suid, @name

        if      @dbname != "master"
                print   "update syslogins set dbname = '%1!' where name = '%2!'",
                        @dbname, @name

       print   "update syslogins set password = %1! where name = '%2!'",
               @password, @name

       if      exists(
               select  1
                       from    master..sysattributes
                       where   object          = @suid
                       and     object_type     = "PS"
                       and     attribute       = 0
                       and     object_cinfo    = "login"
                       and     class           = 14)
       begin
               select  @value  = int_value
                       from    master..sysattributes
                       where   object          = @suid
                       and     object_type     = "PS"
                       and     attribute       = 0
                       and     object_cinfo    = "login"
                       and     class           = 14

               print   "exec sp_modifylogin '%1!','passwd expiration','%2!'",
                       @name, @value
       end

       if      exists(
               select  1
                       from    master..sysattributes
                       where   object          = @suid
                       and     object_type     = "PS"
                       and     attribute       = 1
                       and     object_cinfo    = "login"
                       and     class           = 14)
       begin
               select  @value  = int_value
                       from    master..sysattributes
                       where   object          = @suid
                       and     object_type     = "PS"
                       and     attribute       = 1
                       and     object_cinfo    = "login"
                       and     class           = 14

               print   "exec sp_modifylogin '%1!','min passwd length','%2!'",
                       @name, @value
       end

       if      exists(
               select  1
                       from    master..sysattributes
                       where   object          = @suid
                       and     object_type     = "PS"
                       and     attribute       = 2
                       and     object_cinfo    = "login"
                       and     class           = 14)
       begin
               select  @value  = int_value
                       from    master..sysattributes
                       where   object          = @suid
                       and     object_type     = "PS"
                       and     attribute       = 2
                       and     object_cinfo    = "login"
                       and     class           = 14

               print   "exec sp_modifylogin '%1!','max failed_logins','%2!'",
                       @name, @value
       end

       print   "go"

       fetch   lees into
               @suid, @name, @dbname, @password, @language, @fullname
end

close   lees
go

deallocate cursor lees
go

select  "exec sp_adduser '" + name + "'"
        from    master..sysusers su
        where   su.suid > 2
        and     exists(
                select  1
                from    master..syslogins sl
                where   sl.suid = su.suid)
go

print   "go"
go
EOF

echo    "** Script for logins created"

isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -I ${INTERFACES} -w800 -b <<EOF > roles.sql
set     nocount on
go
declare lees cursor for
        select  srid, name, password, status
                from    master..syssrvroles
                where   srid    >= 32
go

declare @srid           int,
        @name           varchar(30),
        @password       varbinary(30),
        @status         smallint,
        @value          int

open    lees

fetch   lees into
        @srid, @name, @password, @status

while   @@sqlstatus = 0
begin
        print   "create role %1!",
                @name

        if      @password       != null
                print   "update syssrvroles set password = %1! where name = '%2!'",
                        @password, @name

       if      @status & 2 = 2
               print   "alter role %1! lock",
                       @name

       if      exists(
               select  1
                       from    master..sysattributes
                       where   object          = @srid
                       and     object_type     = "PS"
                       and     attribute       = 0
                       and     object_cinfo    = "role"
                       and     class           = 14)
       begin
               select  @value  = int_value
                       from    master..sysattributes
                       where   object          = @srid
                       and     object_type     = "PS"
                       and     attribute       = 0
                       and     object_cinfo    = "role"
                       and     class           = 14

               print   "alter role %1! set passwd expiration %2!",
                       @name, @value
       end

       print   "go"

       fetch   lees into
               @srid, @name, @password, @status
end

close   lees
go

deallocate cursor lees
go

declare lees cursor for
        select  suser_name(suid), role_name(srid), status
                from    master..sysloginroles
                where   suid    > 2
go

declare @suser_name     varchar(30),
        @role_name      varchar(30),
        @status         smallint

open    lees

fetch   lees into
        @suser_name, @role_name, @status

while   @@sqlstatus = 0
begin
        print   "grant role %1! to %2!",
                @role_name, @suser_name

        if      @status = 1
                print   "exec sp_modifylogin '%1!','add default role','%2!'",
                        @suser_name, @role_name

        print   "go"

        fetch   lees into
                @suser_name, @role_name, @status
end

close   lees
go

deallocate cursor lees
go

select  "grant role " + role_name(object_info1) + " to " + role_name(object)
        from    sysattributes
        where   object_type     = "UR"
        and     attribute       = 2
        and     object_cinfo    = null
        and     class           = 8
go

print   "go"
go
EOF

echo    "** Script for roles created"
echo    "Press <ENTER> to continue (delete values at target server)" ; read OK

isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} <<EOF
sp_configure "allow updates to system tables",1
go

delete  master..syslogins where   suid    > 2
go
delete  master..sysalternates
go
delete  master..sysremotelogins
go
delete  master..sysattributes where object_type in ("EL","UR")
go
delete  master..sysloginroles where suid > 2
go
delete  master..sysroles where  id >= 32
go
delete  master..syssrvroles where srid >= 32
go
delete  master..sysusers
        from    master..sysusers su
        where   su.suid > 2
        and     not exists(
                select  1
                        from    master..syslogins sl
                        where   sl.suid = su.suid)
go
delete  master..sysusers
        from    master..sysusers su,
                master..sysroles sr
        where   su.uid  = sr.lrid
        and     sr.id   >= 32
go
EOF

echo "Press <ENTER> to continue (apply generated sql at target server)" ; read OK

isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} < logins.sql
isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} < roles.sql

isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} <<EOF
sp_configure "allow updates to system tables",0
go
EOF

rm -f logins.sql roles.sql

echo "Migration script done."