sp_xp_cmdshell_proxy_account who has been granted to use it?

Question

sp_xp_cmdshell_proxy_account who has been granted to use it?

w.oldoerp

SSC Enthusiast

Points: 192
More actions
September 7, 2014 at 3:59 am

#290495

Hi,
we enabled xp_commandshell Proxi Account on SQL 2008 R2 and use it succesfully with two users.
About the two users we know, because we added them ourself by "Grant exec ON xp_cmdshell to LOGIN"
Questions:
1. How to list the users which have got the Grant to use it?
2. How to remove a user, if it may not have the right any longer?
Thank for hints
Wolfram

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

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

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 1

First of all, you should never ever grant individual users the privs to use xp_CmdShell directly because it provides are very large escalation of privs. Always write the calls to xp_CmdShell in a very well though out and protected stored procedure and give the users the privs to execute that stored procedure.

The opposite of GRANT is REVOKE.

--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)

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

I'll second what Jeff said, you should never grant individuals the right to execute xp_cmdshell. I take it a step further and say you should leave it disabled on your systems and should never even use xp_cmdshell at all, even if you're in the sysadmin Role.

I would strongly urge you to find another way to satisfy your project requirement than to employ xp_cmdshell. There are too many pitfalls and risks associated with bringing it into your solution to convey on a forum.

By the way, because I know sometimes you just gotta get stuff done, here is the answer to your first question:

USE master;

GO

SELECT USER_NAME(grantee_principal_id)

FROM master.sys.database_permissions

WHERE class_desc = 'OBJECT_OR_COLUMN'

AND major_id = OBJECT_ID('xp_cmdshell');

GO

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

w.oldoerp SSC Enthusiast Points: 192 More actions · Answer 3

Thank you both for the answers.

Revoke was to easy 😉

We use xp_cmdshell in this Situation, because we found it the only way to start an Interface to SAP from within a SQL-Server procedure.

First we tried to built a CLR Funktion, which uses the .Net connector from SAP. This failed because the SAP-component is not complete managed code.

So now the Interface is an executable (.Net with Connection via .NET-Connector to SAP and to SQL-Server). We didn't see an other way to start the executable as by xp_cmdshell.

Advantage is that we do not need any additional service or a clientside process to exchange data with SAP System (reading and storing data).

With xp_cmdshell proxi the rights in Windows are limited to the proxi user rights. This Windows user of course has to be set to limited rights in Windows System.

So we think, by the proxi the risks are limited.

Wolfram

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

w.oldoerp (9/8/2014)
Thank you both for the answers.
Revoke was to easy 😉
We use xp_cmdshell in this Situation, because we found it the only way to start an Interface to SAP from within a SQL-Server procedure.
First we tried to built a CLR Funktion, which uses the .Net connector from SAP. This failed because the SAP-component is not complete managed code.
So now the Interface is an executable (.Net with Connection via .NET-Connector to SAP and to SQL-Server). We didn't see an other way to start the executable as by xp_cmdshell.
Advantage is that we do not need any additional service or a clientside process to exchange data with SAP System (reading and storing data).
With xp_cmdshell proxi the rights in Windows are limited to the proxi user rights. This Windows user of course has to be set to limited rights in Windows System.
So we think, by the proxi the risks are limited.
Wolfram

You could avoid having to grant anyone privs to execute xp_CmdShell directly (except "SA") by learning how to use "Execute As Owner" in stored procs.

--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)

w.oldoerp SSC Enthusiast Points: 192 More actions · Answer 5

Thank you Jeff, for that hint.

I will check this as I see two Advantages: better security and easier to implement.

Wolfram