Results 1 to 5 of 5

Thread: dbo user

  1. #1
    Join Date
    May 2009
    Posts
    5

    Unanswered: dbo user

    Hi Gurus,

    I recently inherited an ASE database that hasn't received any love from whom ever was maintaining the system, the current state of the database is equal to a badly written code, no proper indexation, to name a few.

    I been poking around and found one scenario where the "dbo" is assigned to a different user with a 'sa_role', is this a norm?

    Also, further to my investigation, I discover that all users have the 'sa_role'.

    My Question is
    1. Should a normal user have 'sa_role' assigned to them, is this norm?
    2. If #1 is not "standard" what is the quickest way for me to extract all user/login and amend the user role.
    2. What is the standard role for the 'dbo' user?


    Apology if my question are rather noobish, I am not a DBA but got nominated to investigate this environment that is running very slow.

    System Details
    O.S: Windows NT
    ASE version: ASE 11.5


    Appreciate all your help.

    Regards
    Alvin

  2. #2
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97
    You are right: normal users should not have sa_role. Only DBAs should.
    Jumping now to the third question, "dbo" is not a role; a "login" (Sybase ASE term to refer to the user code used for a person or an application to connect to the database; that is, what most products call simply a "user") becomes the dbo of a database if it is defined as the database owner or it is aliased to dbo. Since only one login can be the database owner, to define several dbo the alias method must be followed:
    sp_addalias <loginname>, "dbo"

    Reducing the privileges of existing users is a problem in every system. All ways to investigate the minimum set of privileges they really need are tedious and error prone. I'd suggest a three stage approach:
    1- Based on the knowledge of what the app does, design a first draft of the required privileges.
    2- Involve a (friendly) user in order to test.
    3- Apply the new policies to all. Better not all at the same time, but phasing the change.

    Regards,
    Mariano Corral Herranz

  3. #3
    Join Date
    May 2009
    Posts
    5
    Thanks for your input Mariano

    Quote Originally Posted by corral View Post
    You are right: normal users should not have sa_role. Only DBAs should.
    Jumping now to the third question, "dbo" is not a role; a "login" (Sybase ASE term to refer to the user code used for a person or an application to connect to the database; that is, what most products call simply a "user") becomes the dbo of a database if it is defined as the database owner or it is aliased to dbo. Since only one login can be the database owner, to define several dbo the alias method must be followed:
    sp_addalias <loginname>, "dbo"

    Reducing the privileges of existing users is a problem in every system. All ways to investigate the minimum set of privileges they really need are tedious and error prone. I'd suggest a three stage approach:
    1- Based on the knowledge of what the app does, design a first draft of the required privileges.
    2- Involve a (friendly) user in order to test.
    3- Apply the new policies to all. Better not all at the same time, but phasing the change.

    Regards,
    Mariano Corral Herranz

  4. #4
    Join Date
    May 2009
    Posts
    5
    Just a quick follow-up, is there a best way to extract all user with "sa_role", like one of them sp_ commands?

  5. #5
    Join Date
    May 2013
    Posts
    1
    Quote Originally Posted by dBuff View Post
    Just a quick follow-up, is there a best way to extract all user with "sa_role", like one of them sp_ commands?

    dBuff,

    From memory, try this in master:

    select name from syslogins, sysloginroles
    where sysloginroles.suid=syslogins.suid
    and sysloginroles.srid=0

    Regards.
    Last edited by VX220; 05-28-13 at 18:31.

Tags for this Thread

Posting Permissions

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