April 5, 2011 at 10:03 am
We have many maintenance plan failed after our DBA gone, Since many job owners are owned by her.
I'm now trying to change it back to my account, is this a good practice or any other account should I use?
If we create another domain account for user, then every time I have to login as this user, and modify the maintenace plan, so that the job owner still keeps the same. But this is hard, for everytime I have to login as a user different than my own account by using run as.
I can use SSMS to connect to many servers to do this using that account, but sometimes I do use a lot of remote sessions to look at disk space , so it is not so convenient.
What's the best practice of this?
Thanks
April 5, 2011 at 11:37 am
I don't know that there is a single best practice, but in general I would advocate for having a service account for SQL Server and have the jobs owned by that service account. That way you know what access levels are like on all servers and it's not a person that's going to get fired or quit.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 5, 2011 at 11:43 am
So then the service account has to be a sysadmin, correct?
And everytime I need to change the maintenance job, I have to login to SSMS by using that service account?
I believe some version of SSMS can use run as another user, but some cannot?
Correct?
Thanks
April 5, 2011 at 2:26 pm
When a maintenance plan is created, it will be owned by the user logged into the system when it is created.
The owner will not be changed after the fact. Any other users with the appropriate permissions to edit the maintenance plan can change the plan without updating the owner.
With that said, what I do is use the following to change the ownership of the maintenance plan. Once that is done, the agent jobs need to be recreated to set the owner of the jobs to the new login. All that is needed for this is to edit the sub-plan and save the plan again.
Use msdb;
Declare @owner varbinary(85);
Set @owner = 0x01 -- sa
Update msdb.dbo.sysdtspackages90
Set ownersid = @owner
Where ownersid <> @owner
And packagetype = 6;
-- Display Plans and owners
Select dts.[name]
,dts.[description]
,dts.createdate
,dts.ownersid -- verify this matches above
,p.name As Username -- Will only display if user exists on this server
From msdb.dbo.sysdtspackages90 dts
Left Join sys.server_principals p On p.sid = dts.ownersid
Where dts.packagetype = 6;
Go
If you are on SQL Server 2008 or above, the code needs to be changed to use the sysssispackages table instead of sysdtspackages90.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 5, 2011 at 2:45 pm
This is good to know.
I wonder if using sa is a good practice, read from somewhere, sa account should be disabled for best security.
April 5, 2011 at 2:46 pm
Also is there a query for me to pull all the maintenace plan name, owner name, and jobs for both sql 2005 and 2008?
Thanks
April 5, 2011 at 7:36 pm
I don't see any issues with using 'sa' as the owner of agent jobs.
As for a query, it is included in my previous post.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 5, 2011 at 7:56 pm
Thanks, is there a way also include the job name and its owner in the query too?
April 5, 2011 at 8:05 pm
Not that I am aware of, you can check to see if there is an identifier in the sysdtspackages90 table that would relate to the identifiers in the sysjobs tables.
I don't think there is anything - but I could be wrong.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 6, 2011 at 11:16 am
I cannot find it either.
So there should be a relationship between maintence plan and their jobs, not sure what table to use to query?
Thanks
April 6, 2011 at 12:09 pm
This should get you the JobOwners along with the PackageOwners. I wrote it so it will work in 2008 (adapt for 2005 by referencing the script Jeff provided) and it will return all SSIS Package Types.
-- Display Plans and owners
Select dts.[name]
,dts.[description]
,dts.createdate
,dts.ownersid -- verify this matches above
,p.name As PackageOwner -- Will only display if user exists on this server
,JOB.name as JobOwner
,SSISPackageType =
case dts.packagetype
when 0 then 'default value'
When 1 Then 'SQL Server Import and Export Wizard'
When 2 Then 'DTS Designer in SQL Server 2000'
When 3 Then 'SQL Server Replication'
When 5 Then 'SSIS Designer'
When 6 Then 'Maintenance Plan Designer or Wizard'
End
From msdb.dbo.sysssispackages dts
Left Join master.sys.server_principals p
On p.sid = dts.ownersid
Left Outer Join msdb.dbo.sysjobsteps SJS
On dts.name = substring(SJS.command,CHARINDEX('\',sjs.command)+1,charindex('"',sjs.command,CHARINDEX('\',sjs.command))-CHARINDEX('\',sjs.command)-1)
And SJS.subsystem = 'ssis'
Left Outer Join msdb.dbo.sysjobs SJ
On SJS.job_id = SJ.job_id
Left Outer Join master.sys.server_principals JOB
On JOB.sid = SJ.owner_sid
Go
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 6, 2011 at 1:20 pm
Thanks a lot, I will give it a try.
April 6, 2011 at 3:55 pm
I found a way to relate maintenance plans to their associated agent jobs. It is quite simple...
The job_id is stored in the table: sysmaintplan_subplans and can be used directly to get the actual job created for the sub-plan.
Note, this only works for maintenance plans - and does not give you the job for SSIS packages.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 6, 2011 at 3:58 pm
Jeffrey Williams-493691 (4/6/2011)
I found a way to relate maintenance plans to their associated agent jobs. It is quite simple...The job_id is stored in the table: sysmaintplan_subplans and can be used directly to get the actual job created for the sub-plan.
Note, this only works for maintenance plans - and does not give you the job for SSIS packages.
well that is a bit simpler than what I did for the maint plan at least.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 6, 2011 at 6:10 pm
CirquedeSQLeil (4/6/2011)
Jeffrey Williams-493691 (4/6/2011)
I found a way to relate maintenance plans to their associated agent jobs. It is quite simple...The job_id is stored in the table: sysmaintplan_subplans and can be used directly to get the actual job created for the sub-plan.
Note, this only works for maintenance plans - and does not give you the job for SSIS packages.
well that is a bit simpler than what I did for the maint plan at least.
Yes - but does not include the jobs associated with SSIS packages. Makes sense, because you don't create a job from a package, but you do create a job from a maintenance plan.
I like what you did - but would probably only use it to identify the jobs that are related to any SSIS packages. Since most of the time I only need to worry about maintenance plans - this really never came up. I know what those plan names are and don't really need to get it in a query.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply