Post reply

Effect of Adding SYSADMIN Server Role

  • February 25, 2014 at 7:33 pm

    #304775

    Hi Experts,

    What are the effects of adding SYSADMIN Server Roles ?

    I have this user called "SDE". It has PUBLIC Server Roles. Then I add SYSADMIN. All of a sudden, all application that use SDE cant query data.

    One known effect is that this query "SELECT * FROM city" must be reworded as "SELECT * FROM SDE.city" to make it works.

    I dont think that adding SYSADMIN server role is dangerous. Maybe some one here can explain otherwise...

    thanks

  • February 25, 2014 at 7:46 pm

    #1692119

    default schema. when you added sysadmin it is looking for the table in the dbo schema, not the users schema

  • February 25, 2014 at 9:30 pm

    #1692126

    yocki (2/25/2014)

    Hi Experts,

    What are the effects of adding SYSADMIN Server Roles ?

    I have this user called "SDE". It has PUBLIC Server Roles. Then I add SYSADMIN. All of a sudden, all application that use SDE cant query data.

    One known effect is that this query "SELECT * FROM city" must be reworded as "SELECT * FROM SDE.city" to make it works.

    I dont think that adding SYSADMIN server role is dangerous. Maybe some one here can explain otherwise...

    thanks

    This is one of the many reasons why you should ALWAYS use the 2 part naming convention for all objects in queries. Also, adding the SYSADMIN Server Role to a user is horribly dangerous. [font="Arial Black"]NO ONE [/font]BUT DBAs SHOULD HAVE THE SYSADMIN ROLE.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 25, 2014 at 9:37 pm

    #1692127

    All right.. thanks Eric. I thinks that's why i cant query my table anymore.

  • February 25, 2014 at 9:43 pm

    #1692129

    yes Jeff. you are right. I know i cant give SYSADMIN to some one else.. its just because the operators don't really have good understanding about sql. I grant this sysadmin in order to execute xp_cmdshell to create log file.

    Well, in the end i found another way to create log file without having to grant SYSADMIN to a user

    thanks for warning me anyway... 🙂

  • February 25, 2014 at 10:53 pm

    #1692131

    yocki (2/25/2014)

    yes Jeff. you are right. I know i cant give SYSADMIN to some one else.. its just because the operators don't really have good understanding about sql. I grant this sysadmin in order to execute xp_cmdshell to create log file.

    Well, in the end i found another way to create log file without having to grant SYSADMIN to a user

    thanks for warning me anyway... 🙂

    That would be another thing... never grant anyone privs to run xp_CmdShell directly. It gives folks elevated privs that you can't imagine. Write a stored prodecure that does the job with EXECUTE AS OWNER and then give them privs only to run the stored procedure.

    BTW. Would you share the "another way to create log file without having to grant SYSADMIN to a user", please? Thanks.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply