Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Running a batch job in a sql stored proc Expand / Collapse
Author
Message
Posted Friday, January 25, 2013 10:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 17, 2014 5:26 AM
Points: 9, Visits: 42
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
Post #1411822
Posted Saturday, January 26, 2013 4:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 4:51 PM
Points: 7,097, Visits: 12,597
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
Post #1412004
Posted Saturday, January 26, 2013 9:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1412097
Posted Monday, January 28, 2013 4:33 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:29 PM
Points: 866, Visits: 2,373
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.
Post #1412694
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse