Script to update owner for all SQL Agent Jobs to SA

Problem:

I have many SQL Agent Jobs in my SQL Server and they are running under different accounts, I want to change the owner for all this jobs to SA in one go. I don’t want to manually change owner through ssms for each job.  Is there a script for this.

 

Solution:

Below is the SQL Script to update owner for all SQL Agent Jobs to SA.

First: Script to find out SQL Agent Job details (Name, Status and Owner)

https://exploresqlserver.wordpress.com/2016/11/15/script-to-check-list-of-sql-jobs-their-status-and-owner/

 

USE msdb

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


 

jobdetails1

 

Below is the script to generate job owner update commands for all SQL Agent Jobs to sa

Script:

SET nocount ON

SELECT ‘EXEC MSDB.dbo.sp_update_job ‘ + Char(13)
+ ‘@job_name = ‘ + Char(39) + j.[name] + Char(39)
+ ‘,’ + Char(13) + ‘@owner_login_name = ‘
+ Char(39) + ‘sa’ + Char(39) + Char(13) + Char(13)
FROM   msdb.dbo.sysjobs j
INNER JOIN master.dbo.syslogins l
ON j.owner_sid = l.sid
WHERE  l.[name] <> ‘sa’
ORDER  BY j.[name]

 

jobdetails2

Copy the generated script ,paste in New Query window and execute. This will change all SQL Jobs owner to SA.

 

jobdetails3

 

You can check Job owner details :

USE msdb

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

jobdetails4

 

 

Advertisements

SQL Server – Installing SQL Server 2012 Cluster

Microsoft Cluster

A Microsoft cluster is a high-availability system designed to keep the database up as much as possible. It does this by resuming processing on the secondary server in the event of a failure on the principal server. Since the data resides on a shared disk, a Microsoft cluster will not survive a data corruption.  This technology is high availability, not disaster recovery. All data resides in the same data center and on the same disk. A cluster can be used with other technologies, such as database mirroring.

 

architechture

 

 

In order to Install SQL Server Cluster, we need to have Windows cluster ready as a base with some other pre-requisites as well. [ Installing Windows Cluster is out of scope of this article.]

1- Windows Cluster

2- DTC

3- SQL

 

prerequisites

 

We then need to setup 2 Network Interfaces to be used in production and the other as a heartbeat.

Once the setup is ready to install SQL Server Cluster. Please follow the below steps to configure 2 Node Active/Passive Cluster[SQL Server 2012].

Installation:

Steps to be performed on Node 1.

1. With the SQL Server installation binaries available, right click “Setup” and run as administrator:

1

 

2

 

2.  Click “Installation” in the left menu, then select “New SQL Server Failover Cluster Installation”

 

3

 

3. A check will run in order to find possible constraints when installing the SQL Server Setup support files. At the end of the check, click “OK”

 

4

 

4. Provide product key for your media. Then Next

5

 

5. Accept License Agreement (and the other check if you would like). Then Next

6

6.  Wait for Setup Support Rules check. Once done make sure no failed task. Evaluate warnings for your environment . Then click Next

 

7

 

7. click SQL Server Feature Installation and click Next.

8

 

8. Select Features to install and click NEXT. I have selected all Features.

9

9. Make sure feature Rule check complete with no Failed status. Then Next

 

 

 

10

10. Provide Network and Instance names. Then Next

 

 

 

11

11. Click Next

 

12

12. Click Next

 

 

13

13. Select Disk needed for the resource group . Then Click Next

 

14

14.  Enable IP4 and provide IP address. Then Next

 

15

 

15. Provide Service Accounts (and Collation if different than default – SQL_Latin1_General_CP1_Cl_AS). Then Next

16 17

16. Now we need to choose the authentication mode of our instance.The options are either “Windows Authentication”, which will take benefit from domain and local server logins, or “Mixed Mode”, which accepts Domain/Windows logins as well as logins created and managed by SQL Server.If you pick “Mixed Mode” a login called “sa”, member of the “Sysadmin” role will be enabled. For this reason we need to specify the password for this login.In the box bellow, we need to add all domain/Windows users that have access to the instance and will be part of the “Sysadmin” fixed server role. You need to use the three buttons to add/remove logins from this list:

 

18

17. Select data directories accordingly for Datafile, log file, tempdb files and backup files and click next.

 

19

18. If you have selected analysis features then on this screen add the administrator account and data directory for analysis service. Then click next

 

20

 

 

21

 

19. Select install only for reporting services and Click Next. You can configure reporting services later.

 

 

 

22

20. Check the error report and click NEXT.

 

 

23

21. Make sure no clusters rules failed. Then Next

 

24

22. Click Install.

 

25 26

 

29

23. Click Close to complete the installation.

 

 

30

 

Check the Failover Cluster Manger. And you would see SQL Service and other dependent resources running under it.

 

31

 

 

Adding Node to cluster will be covered in next post.

https://exploresqlserver.wordpress.com/2014/11/11/sql-server-adding-node-to-sql-server-2012-cluster/

 

 Hope this was helpful !!!

 

Installing SQL Server 2008 R2 Cluster

https://exploresqlserver.wordpress.com/2014/08/17/installing-sql-server-2008-r2-cluster/