Menu
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.
337
Using SQL Server Management Studio In Object Explorer, expand a server. Expand Security, and then expand Logins. Right-click the login you wish to add to a SQL Server Agent fixed database role, and select Properties. On the User Mapping page of the Login Properties dialog box, select the row containing msdb.
Login to SQL Server Management Studio. In Object Explorer on the left pane, expand the Databases folder and select the concerned database and navigate to the by expanding Security and Users folders. Right-click the User to which you want to GRANT or REVOKE the permissions.
In addition, there are only two ways that someone can have permission to execute a SQL Agent job. You must either own the job, or be a member of the role SQLAgentOperatorRole (MSDB).
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.
63
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
