November 18, 2005 at 12:24 am
I am getting an error on one of my DB Maint Plan (Integrity Check)SQL SERVER on the SYSTEM1 server. I have tried a couple things including deleting the plan recreating it but I am still getting the same error. It started the day after the change to CCHSD. Do you have any idea what it could be based on the following error message.
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 11/8/2005
Time: 8:00:00 AM
User: N/A
Computer: SYSTEM1
Description:
SQL Server Scheduled Job 'Integrity Checks Job for DB Maintenance Plan 'DB Integrity'' (0x4EF93600CB07CA408D0555E9D9B1EED3) - Status: Failed - Invoked on:
2005-11-08 08:00:00 - Message: The job failed. The owner (CCCHSD\cadams2) of job Integrity Checks Job for DB Maintenance Plan 'DB Integrity' does not have server access.
For more information, see Help and Support Center at
November 18, 2005 at 2:28 am
CCHSD - City of Chicago Health and Safety Department?
I assume you mean you also changed domains in the migration.
You want your maintenace jobs to be owned by sysadmins and run under the context of the SQL Server Agent.
November 18, 2005 at 9:49 am
Yes that is correct. But I found the solution was to add the user with the new domain back to USERS. Apparently when you move SQL SERVER out of one domain to another it dropped the DOMAIN/USER from the table. I added the name back with the new domain CCHSD and the jobs now work fine. Well this was the case in my situation.
November 19, 2005 at 5:37 am
Glad to see you got it working, I'd still encourage you to review how you define ownership on jobs - that looks like a user account, even though we have schemas now to abstract ownership.
November 19, 2005 at 9:53 am
Can you describe more of what you mean by abstracting ownership or direct me towards some reference for futher review? I am working with our NT Admin group and they have other Windows 2003/SQL Server and this resolution did not work for it.
Yes in this example, it was a user account within the domain.
The temporary solution they are doing for the other servers is to run all the jobs as 'sa', but there has to be some other option.
November 21, 2005 at 2:52 am
Sure, in SQL 2000 objects were owned by the user who created them - in 2005 objects are assigned to the default schema of the user who created them.
This gives you a level of abstraction from the individual user not previously possible.
In terms of jobs, I am assuming it was this way in your 2000 cause there werent many other options.
However, there isn't a problem with a job being owned by sysadmins (sa) as long as that job is controlled/scheduled by sysadmins.
November 21, 2005 at 3:03 am
(Sorry lost the thread for a moment there)
In this case, your job is a maintenance task - work to be otherwise to be carried out by the responsible DBAs that you are automating.
Ownership by sa doesn't mean that the sa account is used; you can do this in windows authentation mode if it worries you. It does mean that the job is (by virtue of being owned by a sysadmin) run in the context of the SQL Agent, and therefore can do this kind of work without impedement.
Your controls here are who you allow to run the job - other sysadmins only.
November 23, 2005 at 5:57 pm
in another thread, Drew said the following:
"I went to a Launch event last night, and asked one of Microsoft lot. You can't migrate, but creating new one's is so flexible and easy (using drag and drop objects for each task, creating a flow chart), I was assured it would be better to set them up from new."
Now, in this thread the user does not state what versions of SQL he migrated from/to, in fact, I only see that Joseph makes reference to differences in 2000 and 2005.
It might be wise to note if this was a cross-version migration, in which case, Microsoft has said you can't do that. The original poster said he had tried re-creating the plans and got the same error(s); but he also fixed the initial problem and had additional problems that he's researching with IT.
anyway, please note, no maintenance plan migration from 2000 to 2005. rebuild them.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply