Synchronize a Sybase ASE database sysusers with master..syslogins

Update: Thanks goes to Jeroen Rijnbergen for finding an issue with this script 🙂

suid can be negative (valid values between -32768 and 2147483647) your script only checks for suid > 1, should be: between -32768 and -3 or > 1. With 15.7 and higher where you can specify suid manually with create login, it’s getting more likely to have negative suid for logins. — Jeroen Rijnbergen

isql -Usa -S server -D my_db -i sync_users.sql
exec sp_configure "allow updates", 1
go

/* delete names not found in master syslogins */
delete from sysusers
  where (suid between -32768 and -3 OR suid > 1) and uid < = 16383 and uid > 1
  and name not in (select name from master..syslogins)
go

/* update sysusers and resync all uids from the existing suids */
if exists(select 1 from sysobjects where name = "sysusers_holding" and type = "U")
  drop table sysusers_holding
go

create table sysusers_holding (
  id numeric(6,0) identity primary key,
  oldsuid int null,
  suid int null,
  uid int null,
  gid int null,
  name char(30) null,
  environ varchar(255) null
)
go

/* populate the holding table */
insert into sysusers_holding (oldsuid, gid, name, environ)
  select suid, gid, name, environ 
  from sysusers
  where (suid between -32768 and -3 OR suid > 1) and uid < = 16383 and uid > 1
go

/* update the uids. */
declare syncuser cursor for
 select name, uid from sysusers_holding for update of uid, suid
go

declare @name char(30), @uid int, @suid int
select @suid = 9999

open syncuser
fetch syncuser into @name, @uid

while (@@sqlstatus != 2)
begin
  if exists(select 1 from master..syslogins where name = @name)
  /* got the name update the uid */
  begin
     select @suid = suid
     from master..syslogins
     where name = @name

     -- update sysusers_holding set uid = @suid, suid = @suid where current of syncuser
     update sysusers_holding set suid = @suid where current of syncuser

     if @@error != 0
     begin
       rollback transaction
       break
     end

     select @suid = 9999
  end

  fetch syncuser into @name, @uid
end

close syncuser
go

deallocate cursor syncuser
go

/* now move records over */
delete from sysusers
  where (suid between -32768 and -3 OR suid > 1)
  and uid < = 16383 and uid > 1
go

insert into sysusers
select suid, uid, gid, name, environ
  from sysusers_holding
go

/* sp_helpuser will show any mis-matches) */
exec sp_helpuser
go

exec sp_configure "allow updates", 0
go
Share Button