Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2009
    Posts
    6

    Unanswered: Disabling / Enabling a user in Sybase

    Dear All ,

    Please provide the SQL queries to disable / enable a user in sybase and
    also how can we test it ?

    Thanks
    Suren

  2. #2
    Join Date
    Jan 2009
    Posts
    14
    Enable Disable ??

    Does that mean lock unlock

    Try sp_locklogin <login name>,"lock"/"unlock"

  3. #3
    Join Date
    Jul 2009
    Posts
    6
    Thanks for your reply sir ,

    I want to know is there any feature in Sybase to disable/ enable a user not login .

    The procedure you have mentioned is for locking/ unlocking a login. Am i correct ?

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    sp_dropuser
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Jul 2009
    Posts
    6
    Hi ,

    That would drop the user but i want to revoke all the rights of a user .
    i tried with REVOKE ALL FROM <USER> but that only removes the execute permissions on commands like (Create Database, Create Default, Create Procedure, Create Rule , Create Table , Create View) .

    It doesnot remove the object permissions ie permissions on various table/ views .

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    IMHO:
    - you can do this with a script, that makes a SELECT on data dictionary to obtain all the privileges that user has and then execute REVOKEs for that privileges
    - an easier solution is to drop the user and re-create it again.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  7. #7
    Join Date
    Jul 2009
    Posts
    6
    It would be very humbleof you , if you can post the SQL to know all
    the priviliges/permission a user have ?

    OR you can also point me to a article or tutorial

    Thanks
    Suren

  8. #8
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    This is the table you have to look at:
    SyBooks Online

    A list with all the system tables can be found here:
    SyBooks Online

    But:
    - a user can be part of a group (and that group has its own privileges)
    - some privileges are granted to PUBLIC (so your user will have those too)
    - the login can be part of a role (and that role may have some privileges too)
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  9. #9
    Join Date
    Jul 2009
    Posts
    6
    Thanks alot !!!!

  10. #10
    Join Date
    Jul 2009
    Posts
    6
    Hi aflorin27,


    As suggested by you earlier that the easier option would be drop and recreate the user .

    What would hapen to the objects created and owned by the user? Would they still be existing in the process of drop and re create.

  11. #11
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Well, that's another problem. Sybase documentation
    SyBooks Online
    says:
    You cannot drop a user who owns objects in the database.
    You cannot drop a user who has granted permissions to other users.
    You cannot drop the Database Owner from a database.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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