Update Job owner for all SQL Agent jobs

DECLARE @name_holder VARCHAR(1000)
DECLARE My_Cursor CURSOR
FOR
SELECT [name]  FROM msdb..sysjobs
OPEN My_Cursor
FETCH NEXT FROM My_Cursor INTO @name_holder
WHILE (@@FETCH_STATUS <> -1)
BEGIN
exec msdb..sp_update_job
        @job_name = @name_holder,
        @owner_login_name = 'sa'
FETCH NEXT FROM My_Cursor INTO @name_holder
END
CLOSE My_Cursor
DEALLOCATE My_Cursor

Change Maintenance Plan Owner, Change SSIS Package Owner

If you're used to administering your SQL Servers using Windows Authentication, one side effect is that you'll end up owning a lot of databases, jobs, plans and packages. Sometime this has undesirable effects such as a job or package not working properly. But it's not always obvious how you can change the owner of a package once it's created. Note that it is slightly different for each version of SQL Server.

SQL Server 2008 Maintenance Plan or SSIS Package

--change the owner of a SQL Server 2008 Maintenance Plan or SSIS Package
UPDATE [msdb].[dbo].[sysssispackages]
SET [ownersid] = 0x01 --sa user
WHERE [name] = 'YOUR_MAINT_PLAN_OR_PACKAGE'

SQL Server 2005 Maintenance Plan or SSIS Package

--change the owner of a SQL Server 2005 Maintenance Plan or SSIS Package
UPDATE [msdb].[dbo].[sysdtspackages90]
SET [ownersid] = 0x01 --sa user
WHERE [name] = 'YOUR_MAINT_PLAN_OR_PACKAGE'

SQL Server 2000 Maintenance Plan

--change the owner of a SQL Server 2000 Maintenance Plan
UPDATE [msdb].[dbo].[sysdbmaintplans]
SET [owner] = 'sa'
WHERE [plan_name] = 'YOUR_MAINT_PLAN'

SQL Server 2000 DTS package

--change the owner of a SQL Server 2000 DTS package
--note you need to update the owner column as well
UPDATE [msdb].[dbo].[sysdtspackages]
SET [owner] = 'sa',
 [owner_sid] = 0x01 --sa user
WHERE [name] = 'YOUR_DTS_PACKAGE'

Before I learned this trick, I would literally delete and recreate the package when logged in as SA just to get the package under the proper owner. Otherwise, every time you edit the plan, the job owner will change to match the plan owner which could cause the job to fail.

SQL Job owner issue

select s.name,l.name
 from  msdb..sysjobs s
 left join master.sys.syslogins l on s.owner_sid = l.sid

 select s.name,l.name
from msdb..sysssispackages s
 left join master.sys.syslogins l on s.ownersid = l.sid

 SELECT  s.name ,
        SUSER_SNAME(s.owner_sid) AS owner
FROM    msdb..sysjobs s
ORDER BY name

Common Language Runtime (CLR)

The Common Language Runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.
With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Developers use compiled OO languages like C# or Visual Basic .NET to write code and to have the code executed as if it were a T-SQL procedure, function, or trigger. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.
Managed code uses Code Access Security (CAS), code links, and application domains to prevent assemblies from performing certain operations. SQL Server 2005, SQL Server 2008, and SQL Server 2012 uses CAS to help secure the managed code and prevent compromise of the operating system or database server.

Enable CLR in SQL Server

In SQL Server 2005, SQL Server 2008, and SQL Server 2012, the Common Language Runtime (CLR) is off by default. In an effort to improve security, Microsoft has turned many features "off by default". This is a big change from the old policy of turning every feature on so that developers weren't discouraged from using the feature due to difficulties in getting the feature to work.

EXEC sp_configure 'show advanced options', '1'
Go

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.


Reconfigure
Go

Command(s) completed successfully.


EXEC sp_configure 'clr enabled', '1'
Go

Configuration option 'clr enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.


Reconfigure
Go

Command(s) completed successfully.


EXEC sp_configure 'show advanced options', '0'
Go

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.


Reconfigure
Go

Command(s) completed successfully.