Fix SUIDs

In ASE you may face problems with users not being able to login, this is probably down to logind IDS and database IDS getting mismatched this often happens when a database dump is taken from one server and is loaded to another. You can solve this by either recreating the users in the same odered as they were created in the original server or drop and recreate all the IDS in the database. However there is a script that can do this for you.

sp_configure 'allow update',1
go
use database
go
begin tran
update sysusers
set suid=suser_id(user_name(uid))
from sysusers
where uid between 2 and 16000
and suser_id(user_name(uid)) is not null
go
sp_helpuser
go
/* if no mismatch, commit, else rollback */
commit tran
go
use master
go
sp_configure 'allow update',0
go

or create this in a store prod like this
IF OBJECT_ID('dbo.sp_dba_fixallsuid') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_dba_fixallsuid
IF OBJECT_ID('dbo.sp_dba_fixallsuid') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_dba_fixallsuid >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_dba_fixallsuid >>>'
END
go

create proc sp_dba_fixallsuid
as

declare @invalids int
declare @dbid int
declare @dbo int
declare @dbname char(100)
declare @invalid_offset int

select @dbname=db_name()
select @dbid=db_id()

set nocount on

if (@dbname not in ("master","model","sybsystemprocs","tempdb"))
begin



/* Identify the dodgy users */

select invalid_user_names_found=t.name from
sysusers t
where t.name not in (select name from master..syslogins r
where r.suid = t.suid) and t.suid > 0 and t.uid > 1


/* Get spare suid range for invalids */
select @invalid_offset = max(suid) + 10 from master..syslogins

/* Set all suids to be invalid (this prevents duplicates) */
update sysusers
set suid=suid + @invalid_offset where suid > 0 and suid < @invalid_offset and name <> 'dbo'

update sysusers
set suid=(select m.suid from master..syslogins m where sysusers.name=m.name)
where name in (select name from master..syslogins)

/* Fix the dbo in case he has changed */

select @dbo=suid from master..sysdatabases m where m.dbid=@dbid
update sysusers set suid=@dbo where name = 'dbo'


select dbo_set_to = m.name from master..syslogins m where m.suid=@dbo
select still_invalid_after_fix=name from sysusers where suid > @invalid_offset

/* Inform of the number of alaises unprocessed */

select aliases_unprocessed = count(*) from sysalternates

end
else
print "Not allowed in a system database."

return (0)

go
EXEC sp_procxmode 'dbo.sp_dba_fixallsuid','unchained'
go
IF OBJECT_ID('dbo.sp_dba_fixallsuid') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_dba_fixallsuid >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_dba_fixallsuid >>>'
go
USE sybsystemprocs
go
GRANT EXECUTE ON dbo.sp_dba_fixallsuid TO public
go