December 14, 2009 at 11:29 am
I have a number of old SQL Server 2000's that I must administer. The logins and jobs are all a mess, so I am testing stuff out on my workstation, where I also have SQL 2000 installed.
What I would like to have on my production server is a Group that owns all Jobs, therefore SQL DBA's can be added to and removed from this Group as they come and go. That seems to make sense, no?
But when I create a Login to the SQL 2000 instance, and that Login is a Windows Group, that Group will NOT show up as possible Owner of a new Job. The only Logins that show up under New Job Properties -> General -> Owner are Windows Users. Which means if I have a zillion SQL 2000 servers to administer (which I do!) then every Job will have one guy (me) as the Owner of every job.
Then when I leave, either I have to change them all before I leave, or if I'm mean, all those jobs will fail and will have to be updated by my successor. The whole point of using Groups is to avoid such problems, right?
Am I missing something?
Thanx!
December 15, 2009 at 5:54 pm
Why not make the sa login owner of all the jobs? No one should be using this login and it will have the permissions to run all the jobs without having to change them from you owning them to a successor owning them.
December 15, 2009 at 7:58 pm
the owner of the job, if I remember, doesn't do much in 2000. I think it can only be logins, and I've just used SA as the login.
It seems to me that we used to have to reset this after we edited a job.
December 16, 2009 at 1:19 pm
[font="Verdana"]Everything you ever wanted to know about SQL 2000 Maintenance Plans, Jobs and Owners!
Copy and paste this into SQL 2000 Query Analyzer and GO NUTS!!
(I would actually appreciate your feedback!)[/font]
/*
SQL 2000 Maintenance Plans, Jobs and Owners.
============================================
First note that a SQL 2000 Maintenance Plan is really nothing but a wizard that creates Jobs.
And these Jobs usually do nothing other than call an extended stored procedure called xp_sqlmaint.
And this does nothing but call the command line utility sqlmaint.exe. And that command line utility
has a zillion arguements (documented in BOL).
So if you create a Maintenance Plan and choose lots of options, then open up Jobs, you will see a
bunch of Jobs have been created for this Maintenance Plan. If you look at a Job, you will see it
has an Owner. Go back and look at a Maintenance Plan and you will see it does NOT have an Owner.
Well, not that you can see.
Here's whats really happening: When you create a Maintenance Plan, that Plan does have an owner,
you just don't see it in Enterprise Manager (but it is stored in msdb.dbo.sysdbmaintplans). Then
every one of the Jobs created by this Plan has that same Owner. But you can easily change the
Owner of a Job right there in Enterprise Manager. But you can NOT change the Owner of a Plan in
Enterprise Manager.
And on top of that, you can NOT specify a Group as the Owner of a Job in Enterprise Manager.
(And you cannot specify the Owner of a Maintenance Plan in Enterprise Manager at all).
So the bottom line is, in many enterprises there is a SQL DBA. This person logs into all the SQL
Servers, creates nice Maintenance Plans (which he *secretly* owns), and those Plans create Jobs
(whose owners CAN be seen, and easily changed). Then your DBA splits and gets a better jobs
somewhere warm. So the new DBA merrily changes all Jobs to the his or her login. Everything works
fine until someone fires up the Maintenance Plan and changes anything at all. Then ALL the Jobs
created by this Maintenance Plan revert to the guy laying on the beach, and all the Jobs fail.
Your options are: Keep changing all Job owners, or ditch Maintenance Plans entirely and manually
create Jobs (after geeking out in 10-year old SQL command-line utility funness) or yell at Microsoft
for not allowing you to assign a Group as the Owner of both Maintenance Plans and Jobs (that is the
right way and *would* solve all these problems but... NO!), or upgrade all SQL Servers to 2005 or
later (though THEY still have problems!), or just change everything to be owned by Mr. 'sa' (which
is a security Mega-No-No! *and* you can't just change the sa password because you very well may
not know if the application uses it and what might break, etc), OR YOU COULD USE THE SCRIPTS BELOW.
You're welcome.
:)
REFERENCES.
Here are some articles I borrowed from. Some ONLY apply to 2005 and later,
but were useful in figuring stuff out.
sqlservercentral.com
http://www.sqlservercentral.com/Forums/Topic619202-1435-1.aspx
DYK: Maintenance Plans have Owners
http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/05/04/dyk-maintenance-plans-have-owners.aspx
The Job whose owner kept coming back......
http://sqlblogcasts.com/blogs/acalvett/archive/2007/12/30/the-job-whose-owner-kept-coming-back.aspx
Job Owner Reverts to Previous Owner when Scheduled Maintenance Plan is Edited
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=295846&wa=wsignin1.0
How to easily recreate a maintenance plan (in order to change its job owner)?
http://blogs.microsoft.co.il/blogs/srldba/archive/2008/08/22/Easily-recreate-MP.aspx
POSTED HERE:
*/
/*
*** Show all info about Maintenance Plans, Jobs and the different Owners ***
Step through these one by one to see what is where,
and how everything is related.
*/
-- msdb.dbo.sysdbmaintplans
-- All Maintenance Plans in this SQL 2000 Instance.
-- First show the Maintenance Plan name, then the Owner, then everything:
SELECT
plan_name,
owner,
*
FROM msdb.dbo.sysdbmaintplans
-- msdb.dbo.sysjobs
-- All Jobs in this SQL 2000 Instance
-- (some of which *may* have been created by Maintenance Plans):
SELECT
*
FROM msdb.dbo.sysjobs
-- msdb.dbo.sysdbmaintplan_jobs
-- Join table, establishing a many-to-many relationship between Maintenance Plans and Jobs
-- (even though a Maintenence Plan can contain many Jobs, but a Job belongs to just one
-- Maintenance Plan - unless I'm really missing something!)
SELECT *
FROM msdb.dbo.sysdbmaintplan_jobs
-- Note that msdb.dbo.sysjobs does NOT contain the name of the Owner of each Job,
-- it contains the SID for the owner of each Job, owner_sid. To get the owner name
-- you have to JOIN to either the table master.dbo.sysxlogins, or the view master.dbo.syslogins.
-- (These contain roughly the same data, but I'm not sure what the difference is.)
SELECT
[SysJobs].name AS 'Job Name',
[SysLogins].loginname AS 'Login Name (Owner of Job)',
*
FROM msdb.dbo.sysjobs AS [SysJobs]
LEFT OUTER JOIN master.dbo.syslogins AS [SysLogins]
ON [SysJobs].owner_sid = [SysLogins].sid
-- This shows all the Jobs for each Maintenance Plan, in this SQL 2000 Instance:
SELECT
*
FROM msdb.dbo.sysdbmaintplans AS [Plans]
LEFT OUTER JOIN msdb.dbo.sysdbmaintplan_jobs AS [Jobs]
ON [Plans].plan_id = [Jobs].plan_id
ORDER BY [Plans].plan_name ASC
/*
This gives each Maintenance Plan name, the Owner of the Maintenance Plan, the Owner of each Job
and the name of each Job. If an Owner of a Job is different from the Owner of a Maintenance Plan,
then the next time the Maintenance Plan is modified in any way, it will change the Owner of all
of its Jobs back to the Owner of the Maintenance Plan.
*/
SELECT
[Plans].plan_name 'Maintenance Plan Name',
[Plans].owner AS 'Maintenance Plan Owner',
[SysLogins].loginname AS 'Job Owner',
[SysJobs].name AS 'Job Name'
FROM msdb.dbo.sysdbmaintplans AS [Plans]
LEFT OUTER JOIN msdb.dbo.sysdbmaintplan_jobs AS [Jobs]
ON Plans.plan_id = Jobs.plan_id
LEFT OUTER JOIN msdb.dbo.sysjobs AS [SysJobs]
ON [Jobs].job_id = [SysJobs].job_id
LEFT OUTER JOIN master.dbo.syslogins AS [SysLogins]
ON [SysJobs].owner_sid = [SysLogins].sid
ORDER BY Plans.plan_name ASC
/*
*** UPDATE MAINTENANCE PLAN OWNER ***
*/
/*
Run this first to see if any Maintenance Plan Owners are different than the
Job Owners. (If so, then any edit to the Maintenance Plan will revert all
its Jobs to the Owner of the Maintenance Plan.) A Maintenance Plan may show
up more than once, if it has a number of Jobs.
*/
SELECT
[Plans].plan_name 'Maintenance Plan Name',
[Plans].owner AS 'Maintenance Plan Owner',
[SysLogins].loginname AS 'Job Owner',
[SysJobs].name AS 'Job Name'
FROM msdb.dbo.sysdbmaintplans AS [Plans]
LEFT OUTER JOIN msdb.dbo.sysdbmaintplan_jobs AS [Jobs]
ON Plans.plan_id = Jobs.plan_id
LEFT OUTER JOIN msdb.dbo.sysjobs AS [SysJobs]
ON [Jobs].job_id = [SysJobs].job_id
LEFT OUTER JOIN master.dbo.syslogins AS [SysLogins]
ON [SysJobs].owner_sid = [SysLogins].sid
ORDER BY Plans.plan_name ASC
-- Show all Logins to this SQL 2000 Instance,
-- so you know which Logins are available:
SELECT loginname, sid FROM master.dbo.syslogins
-- Actually update a Maintenance Plan Owner:
UPDATE msdb.dbo.sysdbmaintplans
SET owner = 'DOMAIN\user' --'Copy and Paste loginname here'
WHERE plan_name = 'DB Maintenance Plan1' --'Copy and Paste plan_name here'
/*
*** UPDATE JOB OWNER -- Long Version ***
*/
DECLARE @Job_ID UNIQUEIDENTIFIER -- This is the ID of the Job for which we will update the Owner.
DECLARE @Job_Name NVARCHAR(128)
DECLARE @Old_JobOwner_Name NVARCHAR(128)
DECLARE @New_JobOwner_Name NVARCHAR(128)
DECLARE @New_JobOwner_SID VARBINARY(85)
-- Show all Jobs in this SQL 2000 Instance:
SELECT * FROM msdb.dbo.sysjobs
-- For what Job are we updating the Owner?
SET @Job_ID = 'Copy and Paste job_id here' -- 'Copy and Paste job_id here'
SET @Job_Name = ( SELECT [name] FROM msdb.dbo.sysjobs WHERE job_id = @Job_ID )
-- Grab the old Job Owner name, so we can compare at the end:
SET @Old_JobOwner_Name =
(
SELECT [Logins].loginname
FROM master.dbo.syslogins AS [Logins]
LEFT OUTER JOIN msdb.dbo.sysjobs AS [Jobs]
ON [Logins].sid = [Jobs].owner_sid
WHERE [Jobs].job_id = @Job_ID
)
-- Show it:
SELECT @Old_JobOwner_Name
-- Show all Logins to this SQL 2000 Instance:
SELECT loginname, sid FROM master.dbo.syslogins
-- Who is the new Owner of this Job?
-- NOTE: Just using sa is very poor security practice!
SET @New_JobOwner_Name = 'Copy and Paste loginname here' -- 'Copy and Paste loginname here'
-- Set the @NewJobOwnerName variable:
SET @New_JobOwner_SID = ( SELECT sid FROM master.dbo.syslogins WHERE loginname = @New_JobOwner_Name )
-- and show it:
SELECT @New_JobOwner_SID
-- Do the update:
UPDATE msdb.dbo.sysjobs
SET owner_sid = @New_JobOwner_SID
WHERE job_id = @Job_ID
-- Show the changes:
SELECT
@Job_Name AS 'Job Name',
@Old_JobOwner_Name AS 'Old Owner of this Job',
@New_JobOwner_Name AS 'New Owner of this Job'
/*
*** UPDATE JOB OWNER -- Short Version ***
*/
DECLARE @Job_ID UNIQUEIDENTIFIER -- This is the ID of the Job for which we will update the Owner.
DECLARE @Job_Name NVARCHAR(128)
DECLARE @Old_JobOwner_Name NVARCHAR(128)
DECLARE @New_JobOwner_Name NVARCHAR(128)
DECLARE @New_JobOwner_SID VARBINARY(85)
-- Show all Jobs in this SQL 2000 Instance:
SELECT * FROM msdb.dbo.sysjobs
-- Show all Logins to this SQL 2000 Instance:
SELECT loginname, sid FROM master.dbo.syslogins
-- ****** ENTER INFORMATION HERE ******
-- For what Job are we updating the Owner?
SET @Job_ID = 'Copy and Paste job_id here' -- 'Copy and Paste job_id here'
-- Who is the new Owner of this Job?
SET @New_JobOwner_Name = 'Copy and Paste loginname here' -- 'Copy and Paste loginname here'
-- ************************************
-- Grab the Job Name to use at the end:
SET @Job_Name = ( SELECT [name] FROM msdb.dbo.sysjobs WHERE job_id = @Job_ID )
-- Grab the old Job Owner name, so we can compare at the end:
SET @Old_JobOwner_Name =
(
SELECT [Logins].loginname
FROM master.dbo.syslogins AS [Logins]
LEFT OUTER JOIN msdb.dbo.sysjobs AS [Jobs]
ON [Logins].sid = [Jobs].owner_sid
WHERE [Jobs].job_id = @Job_ID
)
-- Set the @NewJobOwnerName variable:
SET @New_JobOwner_SID = ( SELECT sid FROM master.dbo.syslogins WHERE loginname = @New_JobOwner_Name )
-- Do the update:
UPDATE msdb.dbo.sysjobs
SET owner_sid = @New_JobOwner_SID
WHERE job_id = @Job_ID
-- Show the changes:
SELECT
@Job_Name AS 'Job Name',
@Old_JobOwner_Name AS 'Old Owner of this Job',
@New_JobOwner_Name AS 'New Owner of this Job'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply