Michael L John (3/20/2013)
1. Take the call to xp_cmdshell out of the SP.2. In the SP, test for the condition that would trigger the call to the batch file.
a. Return success if the condition is FALSE
b. Return failure if the condition is TRUE
3. Create a SQL Job.
a. Call the procedure in the first step.
b. In the advanced properties of the step,
1. Set the "on success action" to "Quit the job reporting success"
2. Set the "On Failure Action" to "Go to the next step"
c. In the second step, set the "type" to operating system and call the batch file.
Nicely done and probably the way I'd do it, as well.
By default xp_cmdshell is disabled in SQL 2005 and up. It's a big security issue. Batch files and T-SQL are not meant to play together. They are for two different uses.
I'll have to say that's mostly old wive's tales, myths, and huge misunderstandings. xp_CmdShell is NOT an issue on a properly locked down system. The real problem is that few take the time to actually and properly lock down their system.
Consider this. Who can run xp_CmdShell? Most people will say only those with "SA" privs and they'd be partially correct. If someone is foolish enough to give a low prived user proxy privs to run it, that's another way. Most people very incorrectlyresort to that method when they want a user to be able to run a proc with xp_CmdShell in it and that's probably the worst way to do it.
But, I digress. If you give no one proxy privs and only DBAs have "SA" privs, who can run xp_CmdShell? The answer is only DBAs and an attacker that breaks in as one of those DBAs.
Now, knowing that, you have "wisely" (notice the quotes) turned off xp_CmdShell because it's a "security risk" (notice the quotes again). If an attacker does break into your system as a DBA, then he HAS "SA" privs and can turn it on. In fact, since he's actually expecting it to be off, his attack code is setup to automatically turn it on and he raises holy hell in you system after that.
So what did having xp_CmdShell turned off accomplish? [font="Arial Black"]NOTHING[/font]. It only slowed the attacker down for about 1 ms.
No, let's turn the table. You've very wisely (notice, no quotes) locked down your system so that no apps and no users have anything more than DBO privs (I usually try to restrict to just "PUPLIC" privs but DBO will do for this example). xp_CmdShell is turn on!
Remember, there are no proxied users that can run it. Who, besides, the DBAs, can use xp_CmdShell? Will an attacker that breaks in as a non-DBA user or app be able to run it EVEN IF IT'S TURNED ON?
[font="Arial Black"]NO![/font]
Now, let's just say that you have an app (or user) that must be allowed to run a stored procedure on an ad hoc, on demand basis that has a call to xp_CmdShell in it. What do you do? Give the user "SA" privs? We both know that's stupid. Setup a proxy and give just that user privs to run xp_CmdShell directly? That's even more stupid because it also gives the DBAs a false sense of security.
So what to do?
There are multiple methods (Google them... not trying to write an article here) to setup the stored procedure itself to have the privs to run xp_CmdShell and only in the manner prescribed in the proc. A user with only "PUBLIC" privs and "exec" privs on that proc can run the stored procedure just fine yet they cannot run xp_CmdShell directy. Shoot... they can't even see what's inside the proc. THAT's the right way to use xp_CmdShell!
Like I said, xp_CmdShell is NOT a security risk. Only poor security and a poor understanding of security is a security risk.
--Jeff Moden
Change is inevitable... Change for the better is not.