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

/* 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)

/* 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

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

/* 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

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

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

open syncuser
fetch syncuser into @name, @uid

while (@@sqlstatus != 2)
  if exists(select 1 from master..syslogins where name = @name)
  /* got the name update the uid */
     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
       rollback transaction

     select @suid = 9999

  fetch syncuser into @name, @uid

close syncuser

deallocate cursor syncuser

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

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

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

exec sp_configure "allow updates", 0
Share Button

5 Replies to “Synchronize a Sybase ASE database sysusers with master..syslogins”

  1. Jason,

    Thank you!

    What are the terms of use of procedure?

    1. Personal use only?

    2. Use at client sites as well?

    Under what conditions?

    1. Can we modify it? Like to quote the source? e.g. Jason Froebe, your site etc?

    2. Or do you preffer that we use as is, report bugs and make feature requests
    to you so you can make the update and publish to the community at large?

    3. Can we use it as the base line for our own custom version?

    Thank you Jason.


    1. Consider it public domain. The procedure/method itself is so simple that anyone undertaking the task via SQL will come with it or something similar. Attribution would be nice but isn’t necessary.

Leave a Reply to Jean-Pierre Dareys Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.