SQL Agent job fails | SELECT permission deined on object ‘sysdbmaintplans’

Today’s post is also about SQL Server Agent and jobs not starting up. Again it is on the same instance where SQL Server Agent was not starting and yesterday I had posted on how it was fixed. SQL Server Agent was running fine and all the scheduled jobs were executing successfully except for the Backup Jobs. These jobs were taking the database backups through the Maintenance Plans (sqlmaint.exe). The job history had only the following message.

Executed as user: domain\AgentAccount. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

To get more information related to the Job failure, I enabled Verbose Logging for the SQL Agent job step is shown in the below screenshot.

As expected the Output file of the Job Step had much more details in it.

Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.2039
Copyright (C) Microsoft Corporation, 1995 - 1998
(null)
Logged on to SQL Server 'InstanceName'
as 'Domain\SQLServerServiceAccount' (trusted)
SELECT permission deined on object 'sysdbmaintplans', database 'msdb', owner 'dbo'
Process Exit Code: 1(Failed) (SQLSTATE 01000) (Message 0) slssqlmain failed (SLSTATE HY000) (Error 500000). The step failed.

Permission denied error again? The SQL Server Agent account had sysadmin privileges and some of the other jobs were successful. The catch was this line in the output file.

Logged on to SQL Server 'InstanceName'
as 'Domain\SQLServerServiceAccount' (trusted)

On this instance the Service Accounts for SQL Server and SQL Server Agent services are different. Even though the job owner was SQL Server Agent service account, the job would connect to the Instance using the SQL Server service account credentials. This is because the SQL Server Maintenance Plans make use of sqlmaint.exe which in turn calls xp_sqlmaint an Extended stored procedure. With this understanding it was easy to track down why the Maintenance Jobs were failing. The SQL Server service account was entered as serviceaccount@domain.com.

Since this server had known issues with this naming convention, all it needed was to enter the service account of SQL Server service to domain\serviceaccount.

When the SQL Server Agent service account was changed to follow the correct naming convention, SQL Server had created a new login which had access to SQL Server through Group Membership.

But the SQL Server service account did not have an individual login through Group Membership. Hence even though the Domain Group of which the service account was a member of had access to the instance, it was not getting the requisite permissions because it was unable to authenticate with the Active Directory. It is all because the way the domain and the trust across the domain had been setup.

The Change Control to modify the naming convention of the SQL Server service account was successfully implemented on Sunday and the Maintenance Jobs have run successfully ever since.

Leave a Reply