Command to execute Copy,Delete etc instead of XP_cmdShell

Question

Post reply

Command to execute Copy,Delete etc instead of XP_cmdShell

srajinigandh

Mr or Mrs. 500

Points: 571
More actions
September 17, 2013 at 9:29 am

#298785

Dear All,
We are having legacy Sql server data in Sql 2000 and moving to Sql 2012.Have found usage of XP_cmdshell in many procedures trying to do the following
copy command - files to local drives
erase command - files from local drives
Move
BCP (importing to tables from file)
Ping(just for testing)
dir command
DTSRun
company standards now does not permit usage of XP_cmdshell as it has security flaws, so need to find alternative, could any one please help me on this ? Cannot use Sql agent, as we are going for Autosys . DTS is planned to move to SSI in the next phase.So other than that I found CLR integration , but seems too much of change to procedure and I am new to it. Just wanted to know if there is any command alternative to XP_cmdshell. Please shed some light.
regards
Raj

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

You might want to read this

http://www.sqlservercentral.com/Forums/Topic1173815-392-1.aspx#bm1173927

and determine if the use of !! to execute operating system commands will work in your environment.

If you do test ... please post your result (success or failure) so that others may learn.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

srajinigandh (9/17/2013)
...company standards now does not permit usage of XP_cmdshell as it has security flaws, ...

Sigh....

That's like saying that DELETE has a flaw in it because it will delete all rows if you don't include a WHERE clause. XP_CmdShell isn't "the" security problem. How people misuse is the actual problem and such misuse is pretty easy to prevent.

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

srajinigandh Mr or Mrs. 500 Points: 571 More actions · Answer 3

Hi Ron,

Tried replicaate that proc , but Its not working ,, giving error as Incorrect syntax near '!' :crying:

Any other suggestions please ? I am using Sql SErver 2012

regards

Raj

srajinigandh Mr or Mrs. 500 Points: 571 More actions · Answer 4

Jeff Moden (9/17/2013)
srajinigandh (9/17/2013)
...company standards now does not permit usage of XP_cmdshell as it has security flaws, ...
Sigh....
That's like saying that DELETE has a flaw in it because it will delete all rows if you don't include a WHERE clause. XP_CmdShell isn't "the" security problem. How people misuse is the actual problem and such misuse is pretty easy to prevent.

Hi Jeff,

I am not an expert, but certainly XP_CmdShell has elevated rights and people can misuse which no one can stop it.It would be great if you can suggest some ideas .

regards

Raj

Edit: Saw your query on similar thing ..And I beleive you were similar situation like mine ,so would be great to know how you sorted it

srajinigandh Mr or Mrs. 500 Points: 571 More actions · Answer 5

bitbucket-25253 (9/17/2013)
You might want to read this
http://www.sqlservercentral.com/Forums/Topic1173815-392-1.aspx#bm1173927
and determine if the use of !! to execute operating system commands will work in your environment.
If you do test ... please post your result (success or failure) so that others may learn.

Hi Ron,

Sorry I understood need to enable SQLCMD and it works fine. But for me the situation is have many procedures which has several OS commands and also SQL commands(including cursors) ,And think we cannot keep or it would be challenging for SQLCMD to be ON for executions of procs with OS commands and without OS commands. I am stuck now. Google unable to help too...anyone have idea? please do reply.

regards

Raj