There are actually 3 tables which will need to be copied over and synced up, they are syslogins, syssrvroles and sysloginroles.
The below steps will vary a bit but for example if you are upgrading from Sybase version 12 to 15 then you would do the following steps, for other ASE versions you might need to change the temp table a bit:
Source Server
The first step is to bcp out these tables (syslogins, syssrvroles and sysloginroles) from the source Sybase server and copy the files over to the destination.
e.g bcp master..syslogins out syslogins.out -U<Username> -S<Servername> -n -X
From destination server scp the files across e.g.
scp zkc3yit@gbzzyxsyordad02.gbcaydc.baml.com:/tmp/t/* /tmp/t
.
.
.
Destination Server
Syslogins
- Create the temporary syslogins table on the new server
sp_configure ‘allow updates to system tables’,1
go
USE tempdb
go
/*drop table dbo.temp_syslogins
go
*/
CREATE TABLE dbo.temp_syslogins
(
suid int NOT NULL,
status smallint NOT NULL,
accdate datetime NOT NULL,
totcpu int NOT NULL,
totio int NOT NULL,
spacelimit int NOT NULL,
timelimit int NOT NULL,
resultlimit int NOT NULL,
dbname sysname NULL,
name sysname NOT NULL,
password varbinary(130) NULL,
language varchar(30) NULL,
pwdate datetime NULL,
audflags int NULL,
fullname varchar(30) NULL,
srvname varchar(30) NULL,
logincount smallint NULL,
procid int NULL,
lastlogindate datetime NULL,
crdate datetime NULL,
locksuid int NULL,
lockreason int NULL,
lockdate datetime NULL,
lpid int NULL,
crsuid int NULL
)
LOCK ALLPAGES
Go
You may need to tweak the column types depending on which version of Sybase you are importing from, check their source syslogins table to double check.
- Now bcp in the logins which was bcp’d out on the old Sybase server
bcp tempdb..temp_syslogins in syslogins.out -Usa -P -SMICOY2 -n -X - Make a note of the highest suid currently in master..syslogins
- Check to see which logins already exist in master..syslogins and identify any clashes
select s.suid, t.* from tempdb..temp_syslogins t, master..syslogins s
where t.name =s.name
go
Make a careful note where there is a discrepancy in suid’s. If any entry in source has a higher suid that the current highest suid in master..syslogins (e.g. if the highest current suid is 7 and ipgbmsed2 is 7 in master..syslogins but 8 in temp-syslogins) then you need to make a note to remove any special privileges away from suid 8 in temp_sysloginroles later on, if it has any. If the suid is within the current highest suid’s then it will be removed automatically in the delete before the insert, so just ignore.
also run
select s.name, t.name, t.*
from tempdb..temp_syslogins t, master..syslogins s
where t.suid =s.suid
and t.name not in (select name from master..syslogins)
go
Here if you have a situation where an SUID in source clashes with a new one on the destination (i.e they have different names and the source name does not exist in destination) then you will need to increment the suid number in the temp syslogins and also temp sysloginroles table later on. - From the second part above Increase the suid of any source login which clashed with destination
update temp_syslogins
set suid=3006
where suid=6
go - Delete existing logins which match by name
delete from tempdb..temp_syslogins
where name in (
select t.name from tempdb..temp_syslogins t, master..syslogins s
where t.name =s.name
)
go
- Now copy the syslogins over to the master..syslogins table
insert into master..syslogins
select * from tempdb..temp_syslogins
go
.
.
Syssrvroles
- Create the temp_syssrvroles table
USE tempdb
go
CREATE TABLE tempdb..temp_syssrvroles
(
srid int NOT NULL,
name varchar(30) NOT NULL,
password varbinary(128) NULL,
pwdate datetime NULL,
status smallint NULL,
logincount smallint NULL,
locksuid int NULL,
lockreason int NULL,
lockdate datetime NULL
)
go
- bcp in the entries from source
bcp tempdb..temp_syssrvroles in c1p16_sysroles.out -Usa -P -SMICOY2 -n -X
- Check to see if there are any clashing srid’s in the source compared to destination, it should be ok from ASE15.7 to 16
Select s.srid as master_srid, s.name as master_name,
t.srid as source_srid, t.name as source_name, t.*
from tempdb..temp_syssrvroles t, master..syssrvroles s
where t.srid=s.srid
go
IF there are any clashes from above, which from 15.7 to 16 there should not be, then Update any srid’s which conflict with existing srid’s in master..sysrvroles e.g.
update tempdb..temp_syssrvroles
set srid= 133 where srid=33
go - Check to see if there are clashing role names in the source to destination
Select s.srid as master_srid, s.name as master_name,
t.srid as source_srid, t.name as source_name, t.*
from tempdb..temp_syssrvroles t, master..syssrvroles s
where t.name =s.name
go
If there are any clashes from the above query then we delete the srid from the temp table, we will also later need to update sysloginroles to use the srid which already exists in master.
delete from temp_syssrvroles
where srid=34
go
- Delete the srvroles which already exist in master from the temp table.
delete from tempdb..temp_syssrvroles
where srid <=33 –Biggest srid currently in master..syssrvroles is 33
go
- Copy the roles into master.syssrvroles
insert into master..syssrvroles
select * from tempdb..temp_syssrvroles
go
.
.
Sysloginroles
- Create the sysloginroles table
USE tempdb
go
CREATE TABLE dbo.temp_sysloginroles
(
suid int NOT NULL,
srid int NOT NULL,
status smallint NOT NULL,
predid int NULL
)
Go
- BCP in the entries from source
bcp tempdb..temp_sysloginroles in c1p16_sysloginroles.out -Usa -P -SMICOY2 -n -X - Update the same suid as you did for syslogins
update tempdb..temp_sysloginroles
set suid = 3006
where suid=6
go - Delete any suid’s from temp_sysloginroles which already existed at the start in master..syslogins and master..sysloginroles
delete from tempdb..temp_sysloginroles
where suid <=8 –highest suid recorded earlier
go - Delete any suid’s from temp_sysloginroles which were previously a mismatch between master..syslogins and temp_syslogins
delete from tempdb..temp_sysloginroles
where suid in (10)
go
- Update any srid number which you had to update previously in the syssrvroles step e.g.
update tempdb..temp_sysloginroles
set srid= 133
where srid=33 — Updated srid from tempdb..syssrvroles
go - If there were any incidents of the same role name having different srid’s in syssrvroles (the second check) then you need to update the srid:
update temp_sysloginroles
set srid=33
where srid=34
go - Delete any rows from temp_sysloginroles which doesn’t have a matching suid in master..syslogins
delete from tempdb..temp_sysloginroles
where suid not in (select suid from master..syslogins) - Make sure everything looks ok
select * from tempdb..temp_sysloginroles
go
- Now Copy the roles into master.sysloginroles
insert into master..sysloginroles
select * from tempdb..temp_sysloginroles
go - Remove Update to system tables
sp_configure “allow updates to system tables”, 0
go
.
.
.
Synchronize Logins
The next steps relate to synchronizing the suids after you have loaded the old database into the new server.
- First thing is to enable updates to system catalogs
sp_configure ‘allow updates to system tables’,1
go - Then if you had to alter/increment any suid’s in syslogins earlier then you will also need to do this in the sysusers for any database that has suid of e.g 6:
update sysusers set suid=3006
where suid=6
go - Next check whether there are any aliases set up without matching entries in syslogins:
select * from sysalternates
where suid not in (select suid from master..syslogins);
go
If you find any entries which do not match with anything in syslogins then they should be deleted from sysalternates:
delete from sysalternates
where suid not in (select suid from master..syslogins)
go
- The next check is to find situations where the same name in syslogins and sysusers has a different suid.
select l.suid ‘Login suid’,u.suid ‘User suid’,l.name ‘Login name’, u.name ‘User name’
from
master..syslogins l, sysusers u
where l.name = u.name and l.suid != u.suid and u.name != “dbo”
go
If this finds any mismatches then the following query will resync the suids in the user database with the suid’s in syslogins.
update sysusers
set u.suid = l.suid
from sysusers u, master..syslogins l
where l.name=u.name
go - A follow on check is to see whether there are any users who have a different name to their syslogins name but with the same suid’s.
select l.suid,u.suid,l.name, u.name from master..syslogins l, sysusers u
where l.suid = u.suid and l.name != u.name and u.name != “dbo”
go
if that returns some rows then they need to be deleted from sysusers with the following query:
delete from sysusers
where suid in (select suid from master..syslogins)
and name not in (select name from master..syslogins)
and name != “dbo”
go - You should also check for any users in sysusers which do not match anything in syslogins
select * from sysusers u
where u.suid not in (select suid from master..syslogins)
and name != “dbo”
and suid > 0
go
These can be removed with
delete from sysusers
where suid not in (select suid from master..syslogins)
and name != “dbo”
and suid > 0
go - Disable updates to system tables
sp_configure ‘allow updates to system tables’,0
go