Skip to Content
avatar image
Former Member

Synchronizing ASE syslogins and sysusers after a database load

Greetings,

This issue is as old as ASE itself. I have on multiple occasions dealt with it, and some employers had super secret scripts to perform the recociliation.

I have now been commissioned to resolve the problem once and for all at my current customer site.

Hence, racking my brain for previous experiences and solutions, brainstorming, asking for the feedback of colleagues in terms of how you have dealt with it.

Ideas, tips, suggestions, scripts? Any and all welcome!

Regards,

Jean-Pierre

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 19, 2015 at 04:30 PM
    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Jason,

      Consider i am refreshing the database from PROD - DEV.

      Here my dev has some development ID' s  (This is to reduce DBA work ) . My prod is highly restricted . So i have created some additional user login account and assigned some additional privilege on dev application database  .  As per my understanding this script won't help me in that case . Please correct me if i am wrong .

      Because

      1) PROD doesn't have those additional accounts

  • Oct 19, 2015 at 07:52 AM

    You'll have to synchronize multiple system tables

    If you just load 1 user database and don't want to change syslogins in master database, then you have to check several tables in the user db, including: sysusers, sysroles, sysreferences

    If you use column encryption and if you store your encryption keys in different db, then you also have to check syscolumns

    If you load all user databases, it's easier to sync your master database, check tables:

    - syslogins

    - syssrvroles

    - sysloginroles

    - sysattributes (for remote logins, nested roles, some login options, etc.)

    - sysroles

    - sysusers

    - sysprotects

    Note: directly updating system tables is not supported...

    Add comment
    10|10000 characters needed characters exceeded

    • For encryption keys the encryption key db name is stored in syscolumns.encrkeydb (only if the key is stored in a different db then the object using the encryption key)

      As long as you restore the database containing the encryption keys with the same name, there's nothing you have to change for that in syscolumns for the encrypted table using these keys.

      Only if you would like to restore the encryption key db with a different name, then you might have to update the encrkeydb name in syscolumns.

  • Oct 19, 2015 at 06:08 PM

    Simple way is to use combination of

    - ddglen (utility provided by SAP/Sybase for getting metadata out) and

    - SQL generation for dropping users and groups (from system tables in the ASE database)

    Before loading extract from target database

    - as a precaution you can bcp out (character mode) all system tables from the target database

      this will be handy if things do not go as planned.


    - extract group info (this includes grants for public + groups )

    e.g. ddlgen -U sa -S <Server> -P<passw> -TGRP -N<DBName>.% -o  groups.ddl

    - extract user info (includes group member ship for user )

    e.g. ddlgen -U sa -S <Sever> -P<passw> -TUSR -N<DBName>.% -o  users.ddl

    Before load but run on source side (or on target after the load)

    - generate drop users (using  select on sysusers)

    - generate drop NON-dbo aliases  (using select on sysalternates)

    - generate drop user groups (use select on sysusers with gid = uid  and gid > last system gid)

    On target after the load

    - drop users, aliases and groups using the SQL generated

    - add groups using ddlgen output

    - add users using ddlgen output

    This allows you have local sets of groups and users for each environment (PROD/QA/DEV)

    Note that this method avoided updating system tables directly.

    HTH

    Avinash

    Add comment
    10|10000 characters needed characters exceeded