Running a batch job in a sql stored proc

Question

Running a batch job in a sql stored proc

andy.westgate

SSC Veteran

Points: 253
More actions
January 25, 2013 at 10:22 am

#288455

We have someone who executes a batch job the result of which is some data is moved from txt files into sql tables based on a couple of bits of info entered by the user when prompted in the batch job.
The user's permissions have been removed on this sql database to insert data into these tables.
My question would be, can I just execute the batch job from with in a stored proc or would I run into the same permissions issues? Can the user enter info when the user executes a batch file from a sp? Or would I be better off getting rid of the job and using bulk insert or something instead? I would prefer the former.
Thanks in advance for any information.
Andy

Viewing 4 posts - 1 through 3 (of 3 total)

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

andy.westgate (1/25/2013)
My question would be, can I just execute the batch job from with in a stored proc or would I run into the same permissions issues?

You could but having such a setup implies you'd be enlisting the help of xp_cmdshell or a SQLCLR object with EXTERNAL_ACCESS. Enabling processes running in T-SQL that are invokable directly by a user that can interact with the file system on your SQL Server opens up a plethora of security and auditing concerns for which you will need to address so I would try to avoid those options if at all possible.

Can the user enter info when the user executes a batch file from a sp?

No. Anything kicked off from within a stored procedure would have to be able to run with no user interaction.

Or would I be better off getting rid of the job and using bulk insert or something instead?

BULK INSERT may work but again that command must interact with the file system on the SQL Server, see my comment above.

You could create a stored procedure that is signed by a certificate which would allow the proc to use any of the aforementioned mechanisms to import data without having to grant the users who call it those same permissions. Choose carefully though. I would rather see you re-assign the task to a trusted resource, i.e. someone who has the permissions necessary to run the batch file as it is written, rather than allowing file system access from T-SQL.

You could go a different route altogether and implement a system where the users submit a "request" to execute the job and a backend process that is watching for those requests indirectly (key difference from aforementioned methods) executes the batch file on the user's behalf. A solution like this might involve a blend of T-SQL (to insert the requests into a "request table") and an SSIS package that runs continuously on the server watching for requests in the "request table." It could also be implemented using Service Broker, or even as a stand-alone Windows Service that watches the "request table."

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

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

andy.westgate (1/25/2013)
We have someone who executes a batch job the result of which is some data is moved from txt files into sql tables based on a couple of bits of info entered by the user when prompted in the batch job.
The user's permissions have been removed on this sql database to insert data into these tables.
My question would be, can I just execute the batch job from with in a stored proc or would I run into the same permissions issues? Can the user enter info when the user executes a batch file from a sp? Or would I be better off getting rid of the job and using bulk insert or something instead? I would prefer the former.
Thanks in advance for any information.
Andy

Actually, no. The reason is that there's "info entered by the user when prompted in the batch job". Batch jobs executed from SQL Server cannot be "interactive".

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

Nadrek SSC-Insane Points: 20039 More actions · Answer 3

As an alternate, could you have the user use one or more batch files (more if you need to account for the 'couple bits of information') to load the data via BCP? That way, the user's AD/OS login is responsible for the file system access, and then they can either use Windows (TrusteD) or SQL Server Authentication to your SQL server.