Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Effect of Adding SYSADMIN Server Role Expand / Collapse
Author
Message
Posted Tuesday, February 25, 2014 7:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 1:13 AM
Points: 14, Visits: 49
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
Post #1545193
Posted Tuesday, February 25, 2014 7:46 PM This worked for the OP Answer marked as solution
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 288, Visits: 519
default schema. when you added sysadmin it is looking for the table in the dbo schema, not the users schema
Post #1545197
Posted Tuesday, February 25, 2014 9:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:14 PM
Points: 35,266, Visits: 31,756
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. NO ONE 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545208
Posted Tuesday, February 25, 2014 9:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 1:13 AM
Points: 14, Visits: 49
All right.. thanks Eric. I thinks that's why i cant query my table anymore.
Post #1545209
Posted Tuesday, February 25, 2014 9:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 1:13 AM
Points: 14, Visits: 49
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...
Post #1545211
Posted Tuesday, February 25, 2014 10:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:14 PM
Points: 35,266, Visits: 31,756
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545217
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse