0

After restoring a database from other environment is there a way to map a user in the database to a login in the server. I can drop and recreate or alias but in MS SQL Server there is a way:

EXEC sp_change_users_login 'Auto_Fix', 'user'

I was looking for something similar in SYBASE ASE 16

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.