Map login to user in restored database in SYBASE ASE 16

Sybase (ASE) does not provide any method for syncing system tables between a user db and the master db. It's up to the DBA/dbo to manually fix this after loading a database from a different dataserver (or loading an old db dump after the local dataserver's logins/roles/users/aliases has been (heavily) modified).

There are a few issues you'll want to consider:

• users mapped to the wrong login (need to remap suid)
• users with no login to map to (need to delete)
• (db) roles mapped to the wrong (srvr) role (need to remap id)
• (db) roles with no (srvr) role to map to (need to delete)
• aliases mapped to the wrong login (easier to delete and re-run sp_addalias)
• aliases with no login to map to (need to delete)
• permissions with no associated grantee/granter (after cleaning up sysusers/sysalternates/sysroles)

Assuming you're looking for input/suggestions on how to go about patching sysusers ...

---

Do NOT perform these operations without first:

• testing/verifying in a test database
• making sure all changes are performed within a transaction and said changes are double-checked/verified before issuing a commit tran

Anyone proceeding with the following steps is solely responsible for any adverse outcomes!!

---

NOTE: sysusers contains data on users, groups and roles; the following applies to the entries for users.

Assumptions:

• db user names are the same as the associated login name (ie, sysusers.name should match syslogins.name)
• if logins have been mapped to users with different names (on purpose) ... the following will not work; the DBA will need to have a master list of what logins are supposed to be mapped to what users

General steps involved in patching user entries in sysusers:

-- mismatched sysusers entries select suid,suser_name(suid),name from sysusers where suid > 1 and isnull(suser_name(suid),'DOESNOTEXIST') != name and name not in ('dbo','guest','probe','usedb_user') # operator should review and update this list as necessary go -- assuming sysusers needs to be patched ... -- enable updates of system tables exec sp_configure 'allow updates to system tables',1 go -- perform all updates inside a transaction !!! begin tran go -- update sysusers based on sysusers.name = syslogins.name update sysusers set suid = suser_id(name) where suid > 1 and name not in ('dbo','guest','probe','usedb_user') and suser_id(name) != suid and suser_id(name) is not NULL go -- delete sysusers where there is no syslogins.name = sysusers.name delete sysusers where suid > 1 and name not in ('dbo','guest','probe','usedb_user') and suser_id(name) is NULL /* if any errors, or rowcounts are greater than expected: rollback tran else commit tran */ -- disable updates of system tables exec sp_configure 'allow updates to system tables',0 go