Results 1 to 13 of 13
  1. #1
    Join Date
    May 2009
    Posts
    10

    Unanswered: users can not connect

    why users are not able to access database or connect
    to sybase fails after i load database in sybase 11.9.2? do i need to map users
    or its fine? how to map them?thanks

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Most likely your problem indicates a sysusers mismatch.

    But can you post the error message during the connect?

  3. #3
    Join Date
    May 2009
    Posts
    10
    its lil strange..users with sa role.. they are able to login and connect but other users when try to connect from powerbuilder application getting this message
    "store proc sp_server_info not found.specify ownername.objectname or use sp_help to check weather object exists"
    Still the main question is, do i have to map the users after load database operation? is it mandatory or its fine in sybase 11.9.2??

    Thanks

  4. #4
    Join Date
    May 2008
    Location
    Cambridge
    Posts
    26
    I've tried to duplicate this problem by messing up my sysusers table in sybsystemprocs, but it didn't work.

    You can check you sysusers table with this query:
    select suser_id(suid), suid, user_name(uid), uid from sysusers

    Would be a good idea to run that against both the master database and the sybsystemprocs database (where sp_server_info resides) and check that things tie up.

    It would be useful if you could post the output from this query (sanitised of course) here.

    Bob
    Bob Holmes
    Sybase ASE/Replication Server Administrator
    Digital Data Safe - managed database services
    Email: bob.holmes@ddsafe.co.uk

  5. #5
    Join Date
    May 2009
    Posts
    10
    I think logins are server level and not database level.thats why
    users with sa role are able to login and access but users have specific permissions
    on database level so they r getting failed.

    I ran this query in master and sybsystemprocs databases

    Select suid, user_name(uid), uid from sysusers--no results
    --where user_name ='xyz'

    then I ran same query in that particular db with whom the user was trying to connect
    i got this

    suid user_name uid
    1283 XYZ 1448

    what should i understand from this results...?pleaze explain
    thnaks

  6. #6
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    For the login which is connecting, can you do the following and paste it.

    use db
    go
    sp_helpuser <login_that_is_having_problem>
    go

  7. #7
    Join Date
    May 2009
    Posts
    10
    The command executed successfully with no results returned.

    Users_name ID_in_db Group_name Login_name
    XYZ 1448 public XYZ

  8. #8
    Join Date
    May 2008
    Location
    Cambridge
    Posts
    26
    > I ran this query in master and sybsystemprocs databases
    >
    > Select suid, user_name(uid), uid from sysusers--no results
    > --where user_name ='xyz'

    Could you explain 'no results' as I've hightlighted above.

    Also, when you said you got this error ("store proc sp_server_info not found.specify ownername.objectname...") after a 'load database', which database did you load? A user db? sybsystemprocs? master? Where did the database dump come from - same server or different server? I think we're all assuming a user db dump taken from a different server, but it's very unusual that you are getting an error relating to a proc in sybsystemprocs after a load of a user db. This error suggests that your master and sybsystemprocs db's are not from the same server...
    Last edited by harq; 06-12-09 at 18:25.
    Bob Holmes
    Sybase ASE/Replication Server Administrator
    Digital Data Safe - managed database services
    Email: bob.holmes@ddsafe.co.uk

  9. #9
    Join Date
    May 2009
    Posts
    10
    I think we're all assuming a user db dump taken from a different server


    this is true....

  10. #10
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    What do you mean you are assuming? You dont know where this db got loaded from?

    From the sp_helpuser info, the user info looks alright, but the problem is does public has enough permissions.


    Quote Originally Posted by cottage125
    I think we're all assuming a user db dump taken from a different server


    this is true....

  11. #11
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Another thing to check maybe.

    Every database needs some PowerBuilder stored procs in order for PowerBuilder to work. Please check the PB documentation.

    Maybe the source which you loaded from didnt have it, so it got carried over maybe?

  12. #12
    Join Date
    May 2009
    Posts
    10
    Thanks all for your replies.I really appreciate all your time to read this.
    But i just want to know one standard thing.
    IF I restore user db on dev server from backup of prod.
    do i have to map the logins like in sqlserver?
    is that required in sybase 11.9.2 or not?

  13. #13
    Join Date
    Jun 2009
    Location
    South Africa
    Posts
    33
    Does any other Powerbuilder or Infomaker apps run OK against the server where the database was restored to ?
    If not, chances are good that the Powerbuilder catalog stored procedures have not been installed on this database server.
    The stored procs are all named "sp_pb%" and they are generally created in the 'sybsystemprocs' database, not in any of the user databases.
    To install them on the server, look for a file named PBSYC.sql on your Powerbuilder installation CD, under the Server directory, and run this script against the target DB server. You can also reverse-engineer the current "sp_pb%" procs on the server where the database originates from and apply this sql to the new server. Bear in mind that the procs are version-specific for Powerbuilder, e.g. sp_pb115% procs will be for Powerbuilder 11.5.

    For the users :- you will likely have to re-map them. The easiest is usually using a gui type DBA tool like Sybase Central or DbArtisan.

    You can also generate a script from the current db userlist, assuming that the usernames (as in the db) match the login names:
    use <database>
    go
    select '--'='use '+db_name()+char(10)+'go'
    select '--'='exec sp_dropuser '+name+char(10)+'go'
    from sysusers where uid between 2 and 16383
    go
    select '--'='use '+db_name()+char(10)+'go'
    select '--'='exec sp_adduser '+name+char(10)+'go'
    from sysusers where uid between 2 and 16383
    go

    Use the output from this script and run this in the DB as sa or the DBOwner.
    Bear in mind that it will not create the users in the DB that do not appear as valid server logins.

    You can use something similar to create the "missing" logins :
    use <database>
    go
    select '--'='use master'+char(10)+'go'
    select '--'='exec sp_addlogin '+name+', ''p@$$w0rd'''+char(10)+'go'
    from sysusers where uid between 2 and 16383
    and name not in ( select name from master..syslogins )
    go

    The fact that you get an error executing the "sp_server_info" proc might be purely permission related - sp_server_info is a stock-standard stored procedure that is installed into sybsystemprocs during the ASE install / creation process.

Posting Permissions

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