Home » Databases » Sybase » ASE » Synchronize a Sybase ASE database sysusers with master..syslogins

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

Comments

  1. Ed Bates says:

    VERY useful!! Thank you for the post!

  2. If we map the uid to suid, we break any permissions and ownerships that are explicit to that user id. Having suid and uid not match is not a problem.

  3. Jean-Pierre Dareys says:

    Thanks Jason!

  4. Jean-Pierre Dareys says:

    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.

    Jean-Pierre

    dareys@yahoo.com
    dareys@soaringeagle.guru

    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

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

*
*

Facebook login by WP-FB-AutoConnect