April 13, 2004 at 7:10 am
I have an odd problem.
For some time now I've been running a series of stored procedures whose tasks are to extract data according to fixed criteria and use xp_cmdshell and bcp to generate CSV files for mail merge.
To help me I coded a quick VB front end which summoned the three stored procedures for me.
Due to time constraints, I've now been asked to pass this onto the administration team to run.
This isn't a problem, except that the third stored procedure use xp_cmdshell and bcp to generate 4 text files on the server at a location specified by a parameter.
In order to use xp_cmdshell the logged user needs sysadmin privileges.
I thought I'd solved this by moving the 4 xp_cmdshell commands out into a SQL Job. The routine now sets it's parameters in a one record table and executes the job using sp_start_job. The job then runs the xp_cmdshell commands as 'sa' and then sets a flag in the table to say that it has finished. This then allows the stored procedure to continue.
This worked fine until I switched to a login for a user (who doesn't have sysadmin rights). The program ran with no errors, except that the job never started. Now I discover that users can only run jobs that belong to them.
So, now I'm back at square one, either giving the users somewhat undesirable rights to the database just so the job can be run and the data files created.
Any assistance around this would be appreciated
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
April 14, 2004 at 9:54 am
Look into setting up SQLAgentCmdExec Proxy account. This account will enable non-sysadmins to own jobs that execute OS commands (including xp_cmdshell). In the SQL EM -> Select the SQL Server Agent -> (Properties) -> Job System Tab. Check the non-sysadmin job step proxy account at the bottom. Whichever account you use needs only run as a batch job permissions and any other permissions on the OS and the SQL server that the job needs.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply