February 3, 2010 at 7:34 pm
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:
Craig Outcalt
February 4, 2010 at 9:21 am
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
July 16, 2014 at 10:09 am
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