Home Forums SQL Server 2005 SQL Server Newbies how to run xp_cmdshell via .NET App using non SYSADMIN role RE: how to run xp_cmdshell via .NET App using non SYSADMIN role

  • February 24, 2014 at 5:50 am

    #1691493

    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!