How do I grant access to SQL Server Agent to be able to write/modify system files?

sql sql-server tsql jobs bcp
RJ. picture RJ. · Feb 7, 2013 · Viewed 26k times · Source

I have a job that has a stored procedure that runs BCP to QUERYOUT some data.

If I run the QUERYOUT command by itself, it works.

However, if I try to run it in a JOB, it creates the file but "hangs" and the data is never put in the file. This hangs forever so I usually terminate the BCP.exe.

My question is:

How do I get a SQL job to run BCP to do a QUERYOUT and have the permissions to do so?

The QUERYOUT is going to the C:\ drive (so nothing fancy or anything).

This problem is driving me nuts and from reading online, it looks like the user that runs the jobs needs permissions to the folder. The folder isn't protected or anything.

The owner of the SQL job is part of the SYS ADMIN group in SQL.

Any help would be greatly appreciated.

Answer

cha picture cha · Feb 7, 2013

check what user is assigned to SQL Server Agent service. Open services.msc, locate the SQL Server Agent and check Logon properties. There will be either a LocalSystem user (unlikely, based on what you have described) or another user.

Check that the user has "Write" access to the folder where you are trying to write the BCP dump.

Related questions

Executing SQL Server Agent Job from a stored procedure and returning job result

Need to have a stored procedure that calls a SQL Server Agent Job and returns whether or not the job ran successfully or not. So far I have CREATE PROCEDURE MonthlyData AS EXEC msdb.dbo.sp_start_job N'MonthlyData' WAITFOR …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More