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'."

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'."

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!

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."

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:

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 ?

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:

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."