2005 SQLAgent Job Output files not allowed for non sysadmin

microsoft.public.sqlserver.tools

26 Sep 2006 1:53 PM

SusanMSDN

All,

I have posted this to other listgroups without resolution so here goes.....

We have a system with over 400 jobs. We have 3 environments dev, MO,
production.

The developers create and test the jobs in development and then the DBAs
promote the jobs to MO and Production.

In 2005 if the owner of a job is not a sysadmin they cannot write output
files from the job.

This is causing us huge headaches because now the DBAs have to manually add
output files when moving the jobs to MO and production.

BOL says that this behavior is by design and I have since discovered this
is due to a security bulletin where the concern was that when the file is
written it is done so with the security of the SQLAgent account.

We have tried all the SQLAgent roles and use proxie accounts that have a
great deal of authority but still receive the same message. The proxy
account has ... domain admin... local system admin... SQL Server
sysadmin...has everything.

So...

regardless of what the step does, t-sql, cmdexec, activex, etc...

If the user (job owner) is not in sysadmin within SQL Server, they cannot
write their job output to files, only to the jobhistory table and receive the
message below.

Message
Executed as user: domain\proxieaccount. Warning: cannot write logfile
F:\Apps\Test\adhoc_report\JobResults_adhoc_test.txt. Writing to log files is
only allowed to jobs that are owned by sysadmin. Please consider writing log
to table. The step failed

Any thoughts on permissions that we can grant to bypass this 'by design'
behavior. The workaround to log to jobhistory presents us with issues as we
have done a great deal of automation in the job space.

Thoughts?

S2
Susan Shafer