• 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