Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    5

    Unanswered: Load database retaining the users

    Hi,

    I am working on ASE12.0. I need to load a database but i need to retain the target database's users,roles. Please let me know how to do it.

  2. #2
    Join Date
    Mar 2007
    Posts
    72
    Prem,
    In sybase, bringing over users, logins, roles across servers is not very well supported, but can be done.

    I assume you are loading the DB from one server to another. Basically, you will need all the logins present in the master syslogins otherwise its not going to work obviously. Then, as for roles, you can bcp out the syssrvroles, sysloginroles, sysroles and bcp them in.

    and after you load the database run the following
    sp_configure 'allow updates', 1
    go
    update sysusers set a.suid = b.suid from sysusers a, master..syslogins b
    where a.name = b.name
    go
    sp_configure 'allow updates', 0
    go

    in case the suid in the source and the target are different.

    hope this helps

  3. #3
    Join Date
    Feb 2009
    Posts
    5
    Thanx sudarao

    I did bcp in of
    bcp master..syslogins in syslogin.out -c -b -Sxxx -Usa
    bcp master..sysloginroles in syslogroles.out -c -b -Sxxx -Usa
    bcp master..syssrvroles in syssrvroles.out -c -b -Sxxx -Usa
    bcp margin.dbo.sysusers in sysuse.out -c -b -Sxxx -Usa
    bcp margin.dbo.sysalternates in sysalt.out -c -b -Sxxx -Usa
    bcp margin.dbo.sysroles in sysrole.out -c -b -Sxxx -Usa

    originally there were 45 users in margin database
    After load there were 49 users but when i did bcp in of the above tables, I didnt get 5 users and a group margin_group from the original database. before load.

    Only 40 users from the original database are present.

    Is there something wrong that i did??

    Regards,
    Prem

  4. #4
    Join Date
    May 2008
    Posts
    35
    select name from syslogins
    go

    sp_dropuser 'users'
    sp_dropalias 'alias'
    sp_configure "allow updates to system tables",1
    go
    sp_adduser 'users'
    sp_addalias 'alias'

    Then update the passwords

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •