SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
yocki
yocki
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 63
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28323 Visits: 39955
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
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86652 Visits: 41098
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
yocki
yocki
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 63
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). :-D
So, you think its still save to give this particular user access to xp_cmdshell ? Cool

Thanks alot
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86652 Visits: 41098
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). :-D
So, you think its still save to give this particular user access to xp_cmdshell ? Cool

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search