Job failed - The job owner does not have server access

Question

Job failed - The job owner does not have server access

fiyo

Mr or Mrs. 500

Points: 590
More actions
March 28, 2015 at 8:03 pm

#306172

Hello,
We have SQL Server 2008 running on Windows Server 2008 R2.
We have a maintenance plan that contains 5 jobs all scheduled at different times:
Check Database Integrity, Rebuild Index, Backup Database, Clean history, Backup logs.
Of these 5 jobs, I noticed that the "Check Database Integriy" and "Rebuild Index" jobs failed yesterday but ran successfully the previous two days. The error message "The owner (domain\username) of the job "myMaintenancePlan_Subjob" does not have server access"
The domain user account is for our system admin who left the company last month. He was the one who created the maintenance plan. But I confirmed that the job owner for all 5 jobs is the "sa" account.
I also noticed that the other jobs were ran with our SQL Agent service account.
How could I force the two problem jobs to be run with the agent service account? When a job is created, should we set its job owner to be sql agent service or sa account ?
Thank you all in advance for your assistance
regards
efyuze

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

manish2070 SSC Rookie Points: 41 More actions · Answer 1

Hi

You need to change the [owner_id = 1] in dbo.sssispackages of msdb database. This will ensure that 'sa' is owner of all jobs.

Do let me if you are still facing any issues.

Thank you

fiyo Mr or Mrs. 500 Points: 590 More actions · Answer 2

Hi manish2070,

Thanks for your reply. Actually I was just reading a post on changing the job owner using T-SQL: http://www.sqlservercentral.com/articles/Maintenance+Plans/98189/

I'll try that tomorrow at work. The thing I dont understand is that all the jobs are already running using the sa account - confirmed that via the Management Studio and by using this script that I copied online:

USE MSDB

GO

SELECT GETDATE() AS 'ExecutionTime'

GO

SELECT @@SERVERNAME AS 'SQLServerInstance'

GO

SELECT j.[name] AS 'JobName',

Enabled = CASE WHEN j.Enabled = 0 THEN 'No'

ELSE 'Yes'

END,

l.[name] AS 'OwnerName'

FROM MSDB.dbo.sysjobs j

INNER JOIN Master.dbo.syslogins l

ON j.owner_sid = l.sid

ORDER BY j.[name]

GO

Regards

fiyo Mr or Mrs. 500 Points: 590 More actions · Answer 3

hi again manish2070,

Actually in the msdb.dbo.sysssispackages there is no "owner_id" column. There is only the "ownersid" column. Are you referring to the "ownersid" column?

I just realised the SQL Script that I posted earlier queries data in the MSDB.dbo.sysjobs table. When making a join with the Master.dbo.syslogins table I can easily identify the owner for each job - which points to the sa for all 5 jobs.

But the msdb.dbo.sysssispackages table just lists the name of the maintenance plan (among other existing packages). Trying to make a simple join with the master.dbo.syslogins table and the msdb.dbo.sysssispackages table, I find DO NOT my Maintenance plan in the results. My only conclusion is the package owner (most likely our sys admin who left the company) has had his account removed from syslogins.

So maybe the package owners rights has more "Power" over the job owner's rights??? OMG

jasona.work SSC Guru Points: 50118 More actions · Answer 4

One thing I would suggest checking, is the listed "execute as" account for the Maintenance package itself. I'd be willing to bet it's probably pointing to the old SA. Change it there, and see how things go.

The other thing is, syslogins is deprecated (I believe), you'd be better off checking against sys.server_principals.

Jason

fiyo Mr or Mrs. 500 Points: 590 More actions · Answer 5

Hi Jason

Thank you for your reply. Where do you see the "execute as" option for the maintenance plan? I cannot find that option in the GUI.

Thanks for letting me know about the sys.server_principals, I'll update my script to use that view instead.

I'll be using this script to update our maintenance plan owners to sa. I've read alot of discussions regarding using sa or a service account. For now I'll be using the sa account:

--VIEW package owners

------------------------------

use msdb

go

SELECT sysPack.name as 'Package-Plan Name', sysLogins.name as 'Owner Name' from dbo.sysssispackages sysPack

Inner join sys.server_principals [sysLogins] On

sysPack.ownersid = [sysLogins].sid

--UPDATE package owners

-------------------------------

Update dbo.sysssispackages

Set [ownersid] = SUSER_SID ('sa')

Where name = 'myMaintenancePlan'

I'll be doing this update this week. I'll report back if we have any problems or not. If this works, I'm planning to use this script to update all existing packages (SSIS included) created by developers or other system admins. Maybe have a job run weekly to check the job/package owners and update