home
training courses
why choose us?
solutions
support
company
LearnItFirst User Forum
Welcome Guest
Search
|
New Posts
|
Members
|
Log In
|
Register
SQL Server Forum - LearnItFirst.com
»
SQL Server Scripts, Code Samples and SSMS Custom Reports
»
All SQL Server Versions
»
SQL Script: Assign All Jobs to 'sa' Account and Notify Operator On Completion
SQL Script: Assign All Jobs to 'sa' Account and Notify Operator On Completion
Options
Previous Topic
·
Next Topic
Scott Whigham
Posted:
Wednesday, May 02, 2007 1:43:10 PM
Rank: Super Mod
Joined: 3/20/2006
Posts: 460
Points: 1,002
Where do you live?: Dallas, TX
I needed to do this today as I moved my master from one machine to another. I thought I'd put this here so that, the next time I need it, it's here!
Code:
/*
Author: Scott Whigham FROM
http://www.LearnSqlServer.com/
Description: This script changes ownership of all jobs to 'sa' and also changes all jobs to notify an operator ("Scott") on complete
assigns the 'sa' account to all jobs and notifies an operator 'Scott' on completion
Versions: SQL Server 2005 and should work for 2000 but have not tested
Creation Date: May 2, 2007
For more scripts like this one, visit
http://forums.learnsqlserver.com/codesamples.aspx
*/
SET NOCOUNT ON
DECLARE @Jobs TABLE (job_id UNIQUEIDENTIFIER)
INSERT @Jobs (job_id)
SELECT job_id FROM msdb.dbo.sysjobs
DECLARE @job_id UNIQUEIDENTIFIER
SELECT TOP 1 @job_id=job_id FROM @Jobs
WHILE @job_id IS NOT NULL
BEGIN
EXEC msdb.dbo.sp_update_job @job_id=@job_id,
@notify_level_email=3,
@notify_level_netsend=2,
@notify_level_page=2,
@notify_email_operator_name=N'Scott', -- change with your operator name
@owner_login_name=N'sa' -- the login to change the ownership
DELETE @Jobs WHERE job_id=@job_id
SET @job_id = NULL
SELECT TOP 1 @job_id=job_id FROM @Jobs
END
GO
Back to top
Users browsing this topic
Guest
Forum Jump
SQL Server Database Administration
- General SQL Database Question & Answer
- Backup, Recovery and Disaster Recovery
- SQL Server Security
- Integration Services (SSIS) and DTS
Transact-SQL Programming
- DML (SELECT, INSERT, UPDATE, DELETE) Questions
- Stored Procedures, Triggers, & Functions
SQL Server Scripts, Code Samples and SSMS Custom Reports
- All SQL Server Versions
- SQL Server 2005/2008
- SQL Server Management Studio Custom Reports
Customer Service
- Video Requests
You
cannot
post new topics in this forum.
You
cannot
reply to topics in this forum.
You
cannot
delete your posts in this forum.
You
cannot
edit your posts in this forum.
You
cannot
create polls in this forum.
You
cannot
vote in polls in this forum.
SQL Server 2005 DBA Training Videos
SQL Server 2008 DBA Training Videos
Watch this topic
RSS Feed
Email this topic
Print this topic
Threaded
Normal