Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 337
JJ. Avatar asked Feb 07 '13 02:02

JJ.


People also ask

How do I give access to SQL Agent?

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.

How do I provide grant permissions in SQL Server?

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.

What permissions are needed to run a SQL Agent job?

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).


1 Answers

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.

like image 63
cha Avatar answered Nov 15 '22 05:11

cha