Tuesday, 17 May 2011

Writing to log files is only allowed to jobs that are owned by sysadmin

Hello everyone,

I am experiencing an issue with SQL Server Agent and its output file. I have a job that runs daily and I need to store its output to txt file on a shared disk in a clustered environment. The job runs the simple script "EXEC sp_help_revlogin" which is used to generate "CREATE LOGIN" scripts for each SQL user account created on that instance of SQL server (domain or local). The goal of this script is to have current SQL login information in case of a disaster recovery scenario. There is another SQL instance on the remote site to which the databases are replicated using SQL Database Mirroring, but  Database Mirroring  is not replicating SQL user accounts because they are actually stored in the master database which is not supported in Database Mirroring configuration.

The problem I am experiencing is that the job is not creating any output file. The output should contain the SQL script generated by the sp_help_revlogin function where each local login is scripted so it can be ran on the remote SQL server instance. The error message I am getting is:

Executed as user: DOMAIN\user. Warning: cannot write logfile E:\DR_SCRIPTS\DR_CREATE_LOGIN.txt. Writing to log files is only allowed to jobs that are owned by sysadmin. Please consider writing log to table.  The step succeeded.

The user that owns this job is member of the sysadmin server role and it is even a member of the local administrators group on the SQL cluster nodes. So my guess is that this is simply a bug in the SQL Server 2008 (for the reference, build number we are running is 10.0.4000). Even the SQL Server Agent service is running under the account that is a member of the sysadmin role and is the local administrator on both of the SQL cluster nodes.

The solution that worked for me was to change the owner of that SQL Server Agent job to the sa account. After I did that, the job successfully created the output file to the destination folder.

If someone has some deeper explanation why is this happening, I would greatly appreciate it.