how to run xp_cmdshell via .NET App using non SYSADMIN role

Question

how to run xp_cmdshell via .NET App using non SYSADMIN role

yocki

SSC-Addicted

Points: 473
More actions
February 23, 2014 at 11:40 pm

#289866

Dear masters,
I have a stored procedure (SP) that contains "EXEC xp_cmdshell eror msg >>D:\logfile.txt, no_output"
Whenever i call/run the SP, via .NET App and SSMS directly, using user that has (Server) Login Properties --> Server Role: SYSADMIN, it works.
But, when I run using other user (that doesn't have the SYSADMIN role) it only works when executed via SSMS only. If i execute via .NET App, although this user has (Server) Login Properties --> User Mapping: DB_DDLADMIN, it doesnt work.
There is error "The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'."
How can I run this xp_cmdshell without having SYSADSMIN role ?
thanks

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

yocki (2/23/2014)
Dear masters,
I have a stored procedure (SP) that contains "EXEC xp_cmdshell eror msg >>D:\logfile.txt, no_output"
Whenever i call/run the SP, via .NET App and SSMS directly, using user that has (Server) Login Properties --> Server Role: SYSADMIN, it works.
But, when I run using other user (that doesn't have the SYSADMIN role) it only works when executed via SSMS only. If i execute via .NET App, although this user has (Server) Login Properties --> User Mapping: DB_DDLADMIN, it doesnt work.
There is error "The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'."
How can I run this xp_cmdshell without having SYSADSMIN role ?
thanks

te recommended way is to have your stored procedure signed by certificate, which allows the proc to execute under enhanced credentials; Tutorial: Signing Stored Procedures with a Certificate

A band-aid is to create a sysadmin user, and use EXECUTE AS that superuser in the procedure.

The worst thing to do, but it is possible, is to grant execute on xp_cmdshell to your user.

aside from that, it's doubtful you really need xp_cmdshell usage in the first place. if you just need to write to a file, there ar elots of ways to do that, including CLR procs, writing to a table and having a job write it out via powershell, and so many other possibilities.

what, exactly are you doing with the command line?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 2

The certificate method is a real PITA and mostly unnecessary. If you write a stored procedure that does the work and include EXECUTE AS OWNER and the database is owned by a login having SA privs (hopefully, the owner is "SA" and that account is disabled), then the app won't need any privs except privs to run the stored procedure.

Whatever you do, do NOT give the app privs to execute xp_CmdShell directly.

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

yocki SSC-Addicted Points: 473 More actions · Answer 3

Hi Lowell,

Hi Jef Moden,

Thanks for the reply...

What I am trying is to create a text file containing a message why the insert process failed. Here is a bit of the pseudocode:

IF condition1 = 0

BEGIN

xp_cmdshell write to a file "this value is 0"

END

IF condition <>0

BEGIN

INSERT INTO tabel

END

This stored procedure is actually executed by an internal .NET application. Actually I want to avoid granting execute to xp_cmdshell, BUT i have no idea how this application can capture the feedback from the SP that has been executed so I use cmdshell to pool the feedback. This is actually what i want to do:

IF condition1 = 0

BEGIN

set @erorlog = @erorlog + "this value is 0"

END

I want the application to be able to read this "@erorlog" variable. How can i achieve that ?

I do not want to create a new table just to pool the error messages.

Also, I kinda know the danger of this (http://stackoverflow.com/questions/3797036/sql-server-why-xp-cmdshell-is-disabled-by-default) but my users are really really just operator. They have the very most basic skill of SQL (even dont have at all). 😀

So, you think its still save to give this particular user access to xp_cmdshell ? 😎

Thanks alot

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

yocki (2/24/2014)
Hi Lowell,
Hi Jef Moden,
Thanks for the reply...
What I am trying is to create a text file containing a message why the insert process failed. Here is a bit of the pseudocode:
IF condition1 = 0
BEGIN
xp_cmdshell write to a file "this value is 0"
END
IF condition <>0
BEGIN
INSERT INTO tabel
END
This stored procedure is actually executed by an internal .NET application. Actually I want to avoid granting execute to xp_cmdshell, BUT i have no idea how this application can capture the feedback from the SP that has been executed so I use cmdshell to pool the feedback. This is actually what i want to do:
IF condition1 = 0
BEGIN
set @erorlog = @erorlog + "this value is 0"
END
I want the application to be able to read this "@erorlog" variable. How can i achieve that ?
I do not want to create a new table just to pool the error messages.
Also, I kinda know the danger of this (http://stackoverflow.com/questions/3797036/sql-server-why-xp-cmdshell-is-disabled-by-default) but my users are really really just operator. They have the very most basic skill of SQL (even dont have at all). 😀
So, you think its still save to give this particular user access to xp_cmdshell ? 😎
Thanks alot

Like I said, I wouldn't grant xp_CmdShell privs to any user (login, etc). I would build a stored procedure that does only what is need with xp_CmdShell, include EXECUTE AS OWNER in the proc, and the grant the user privs to only run the stored proc.

NEVER grant anyone or any login privs to run xp_CmdShell (SA's will be the obvious exception). Always do it through stored procedures.

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