Change owner of the Maintenance plan

Sometimes when we create a maintenance plan in SQL server, the owner of the maintenance plan will remain same as the user who creates the plan. If the user's account is deleted from the active directory, in that case the job associated with the maintenance plan will fail with below error:

Message
The job failed.  Unable to determine if the owner 
(domain/xxx_user) of job Backup.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user 'domain/xxx_user', error code 0x5. [SQLSTATE 42000] (Error 15404)). 

To avoid such situation, change the owner of the maintenance plan to 'sa' by executing below update command when you creates the maintenance plan.

--SQL 2008 Server

UPDATE msdb.dbo.sysssispackages
SET [ownersid]=0x01
WHERE [name]='YourMaintPlanName'


--SQL 2005 Server

UPDATE [msdb].[dbo].[sysdtspackages90]
SET [ownersid] = 0x01 --sa user
WHERE [name] = 'YOUR_MAINT_PLAN_OR_PACKAGE'

--change the owner of a SQL Server 2000 Maintenance Plan

UPDATE [msdb].[dbo].[sysdbmaintplans]
SET [owner] = 'sa'
WHERE [plan_name] = 'YOUR_MAINT_PLAN'

--change the owner of a SQL Server 2000 DTS package
--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'

cannot execute xp_cmdshell. Access is denied while copying data over the network

Today i faced a problem, i was trying to copy some of the files from one server to another server using xp_cmdshell, while executing it was giving error that "Access is denied while copying data over the network".

Here is the T-SQL that i was trying to execute.

Exec xp_cmdshell 'copy D:\SQLBACKUP\Test.bak \\TEST-10\SQLBACKUP'

Steps need to check
1. Check  xp_cmdshell is enabled or not  in server configuration

2. Check if required user has a permission on destination folder.

3. Third and important point is that SQL Server (MSSQLSERVER)  should run under some domain account(service account). It should not run under Local System.

If SQL service is running under Local system, xp_cmdshell will not be able to copy any files to the network location. xp_cmdshell can only copy to the local drive.