Easy Error Trapping When Using xp_cmdshell

  • xp_cmdshell can be a safe and useful thing to turn on.

    Don't create a proxy account and don't grant anyone into SA that shouldn't be.

    wow... that was easy.

    and the first person that says that people can elevate priviliges to sa through sql injection or buffer overflows is going to get one of these: :doze:

  • From my earlier post:

    "The biggest problem with security is apathy and lazyness. FAR too many people use highly privileged accounts for app logins, OR their application is architected in such a way that requires excess rights."

    You are generally right in your post, but the above covers what often happens..

    CEWII

  • Back to the subject... which was, how to find errors when running BCP from xp_cmdshell...

    You can run into trouble if you are trying to nest the INSERT...EXEC logic. Run...

    CREATE PROCEDURE p_foo3

    AS

    CREATE TABLE #foodata3 (abc CHAR (1))

    INSERT INTO #foodata3 (abc)

    SELECT 'A'

    SELECT *

    FROM #foodata3

    GO

    EXEC p_foo3

    GO

    abc

    ---

    A

    CREATE PROCEDURE p_foo2

    AS

    CREATE TABLE #foodata2 (abc CHAR (1))

    INSERT INTO #foodata2 (abc)

    EXEC p_foo3

    SELECT *

    FROM #foodata2

    GO

    EXEC p_foo2

    GO

    abc

    ---

    A

    CREATE PROCEDURE p_foo1

    AS

    CREATE TABLE #foodata1 (abc CHAR (1))

    INSERT INTO #foodata1 (abc)

    EXEC p_foo2

    SELECT *

    FROM #foodata1

    GO

    EXEC p_foo1

    GO

    Msg 8164, Level 16, State 1, Procedure p_foo2, Line 5

    An INSERT EXEC statement cannot be nested.

    (0 row(s) affected)

    (0 row(s) affected)

    If you have a procedure that moves data via BCP and you insert the output into a table, and then you call the procedure from another procedure and try capturing the output in the same manner, it doesn't work.

    I'd always considered this behavior of SQL Server's as an arbitrary limitation they would someday fix. But apparently not. Too busy adding features we don't need to fix a a shortcoming in a feature we might actually use?

Viewing 3 posts - 16 through 17 (of 17 total)

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