Sql-server – SQL Agent Proxy – writing to logs is only allowed to jobs that are owned by sysadmin

jobspermissionssql serversql-server-2012sql-server-agent

I have a Windows 2012 RS and SQL Server 2012 Enterprise Edition. I have SQL Server maintenance jobs from ola.hallengren.com and the owner of the jobs were sa.

The security team decided to change that owner to a SQL login called SQLUSERID. It is DB owner for the databases that maintenance job runs and no server level access.

I was asked to create a SQL Agent proxy SQLJobs_Proxy and created a credential DBAAdmin_Creditional using Domain\sql_service_account which is a sysadmin and a memeber of local admin group. I created this proxy under Operating System(cmdExec) and SQLUSERID as the principal. In the job property I Type is selected Operating System(cmdExec and Run As SQLjobProxy.

When I execute the job I get the following error:

Executed as user: Domain\sql_service_account.
Warning: cannot write logfile:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\Indexrebuild_83722.txt.
Writing to log files is only allowed to jobs that are owned by sysadmin.
Please consider writing log to table. The step failed.

Domain\SQL_Service_Account is sysadmin and local admin so it has full access to the folders and should be able to write.

What can I do to run the jobs successfully with this SQL login that is not a domain user?

Best Answer

I would work on educating your security team about jobs...they are server/instance level objects, not database level objects, so db_owner seems severely limited.

Ola recommends sysadmin for the job owners:

Which permissions are needed for the SQL Server Maintenance Solution to work?

If you are using SQL Server Agent, the jobs run under the SQL Server Agent service account that is a member of the sysadmin server role. If you are using a proxy account, I recommend that the account be a member of the sysadmin server role.

If you are using another scheduler, I recommend that the scheduler run under an account that is a member of the sysadmin server role.

If you need to have users execute the stored procedures ad hoc against specific databases, these permissions are needed:

DatabaseBackup: sysadmin

DatabaseIntegrityCheck: EXECUTE on dbo.DatabaseIntegrityCheck, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases

IndexOptimize: EXECUTE on dbo.IndexOptimize, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases

Regarding the comment about the msdb filling up if you run against a table, when you install his jobs using his big script MaintenanceSolution.sql he installs a CommandLog Cleanup job that will clear out the tables used to log his job steps.

Related Question