• jghali (7/31/2015)


    Oh well... :ermm:

    In my case, I'm using SQL Express and don't have any other solution than using the xp_cmdshell to use bcp...

    I'll continue searching for the best way to secure the usage of xp_cmdshell.

    Thank you ... and I really enjoyed the thread.

    😉

    The best way is also the simplest way. Go ahead and create the proxy user that most people will tell you to create but DO NOT GIVE ANYONE PRIVS TO RUN XP_CMDSHELL DIRECTLY. Instead, give them permission to run a stored procedure that does only what it's supposed to do with XP_CmdShell, add WITH EXECUTE AS OWNER to the proc, and then give folks privs to execute the proc. Make sure that your proc doesn't take any input that will be used by XP_CmdShell or carefully sanitize it to ensure that there is no SQL Injection and no DOS Injection attempts. If there are, then do nothing and return nothing. You don't want to give a hacker any hints.

    Please see (attached) the presentation I did just one time where I explain how to do all of this. Most of it is to help dispel some of the FUD around the tool. I do, however, explain how to properly set it up and use it safely near the end.

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