InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 01:15:56 GMT20http://www.sqlservercentral.com/Forums/Topic1411822-149-1.aspxAs 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.Mon, 28 Jan 2013 16:33:17 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic1411822-149-1.aspx[quote][b]andy.westgate (1/25/2013)[/b][hr]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[/quote]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".Sat, 26 Jan 2013 21:06:36 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1411822-149-1.aspx[quote][b]andy.westgate (1/25/2013)[/b][hr]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?[/quote]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.[quote]Can the user enter info when the user executes a batch file from a sp?[/quote]No. Anything kicked off from within a stored procedure would have to be able to run with no user interaction.[quote]Or would I be better off getting rid of the job and using bulk insert or something instead?[/quote]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 [i]indirectly[/i] (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."Sat, 26 Jan 2013 04:28:04 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1411822-149-1.aspxWe 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.AndyFri, 25 Jan 2013 10:22:31 GMTandy.westgate