Our SQL 2005 server has a Maintenance Plan that cannot be deleted.
我们的SQL 2005服务器具有无法删除的维护计划。
When I try and delete it via Management Studio I get the following error:
当我尝试通过Management Studio删除它时,我收到以下错误:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) -> The SELECT permission was denied on the object 'sysmaintplan_plans', database 'msdb', schema 'dbo'. (Microsoft SQL Server, Error: 229)
执行Transact-SQL语句或批处理时发生异常。 (Microsoft.SqlServer.ConnectionInfo) - >对象'sysmaintplan_plans',数据库'msdb',架构'dbo'上的SELECT权限被拒绝。 (Microsoft SQL Server,错误:229)
Its SQL 2005 Enterprise Edition version 9.0.3042.0
其SQL 2005企业版版本9.0.3042.0
I have been able to modify the plan so that it doesn't do anything, but I can't rename it or delete it.
我已经能够修改计划,使它不做任何事情,但我无法重命名或删除它。
I have a hunch that this might be something to do with Service Pack 2, as the Maintenance Plan may have been created before SP2 was applied, and I've heard some people had problems with that scenario.
我有一种预感,这可能与Service Pack 2有关,因为维护计划可能是在应用SP2之前创建的,而且我听说有些人在这种情况下遇到了问题。
edit: I am logged in as sa
编辑:我以sa登录
3 个解决方案
#1
Thanks to SqlACID for the suggestion to fix this (see the question comments)
感谢SqlACID提出解决此问题的建议(请参阅问题评论)
I had been using Management Studio on a separate machine.
我一直在一台单独的机器上使用Management Studio。
Running Management Studio on the Server itself (and logging in as SA) allowed me to delete the stubborn Maintenance Plan.
在服务器本身上运行Management Studio(并以SA身份登录)允许我删除顽固的维护计划。
#2
I was not able to delete a Maintenance Plan because of a permission issue. "Authentication failed for user SA" - and I was logged in as SA using Management Studio on the server.
由于权限问题,我无法删除维护计划。 “用户SA的身份验证失败” - 我使用服务器上的Management Studio以SA身份登录。
Here is how I was able to fix the problem:
以下是我能够解决问题的方法:
- Open the problem Maintenance Plan.
- Add a new (empty) Subplan.
- Delete the first (existing) Subplan.
打开问题维护计划。
添加一个新的(空)子计划。
删除第一个(现有)子计划。
As a precaution I also did this:
作为预防措施,我也这样做了:
- Click the Manage Connections button.
- Click Edit in the Manage Connections dialog box.
- Overwrite the SA user name and password.
- Click OK > Close to get back to the Maintenance Plan.
单击“管理连接”按钮。
单击“管理连接”对话框中的“编辑”。
覆盖SA用户名和密码。
单击确定>关闭以返回维护计划。
Finally
- Save the Maintenance Plan.
- Delete the Maintenance Plan.
保存维护计划。
删除维护计划。
Hope this helps.
希望这可以帮助。
#3
I did not have success with Pete's suggestion, but I did have success following the steps here: https://social.msdn.microsoft.com/forums/sqlserver/en-US/ae6f809f-ef74-4534-8327-b7b120a2d700/unable-to-delete-a-sql-2005-maintenance-plan
我对Pete的建议没有成功,但我确实按照这里的步骤取得了成功:https://social.msdn.microsoft.com/forums/sqlserver/en-US/ae6f809f-ef74-4534-8327-b7b120a2d700/unable -to-删除-A-SQL-2005-维修计划
see the post by kahil kahalas:. it says:
看到kahil kahalas的帖子:它说:
You can also delete SQL 2005 Maintenance Plan using T - Sql.
您还可以使用T-Sql删除SQL 2005维护计划。
We need to perform the below steps to delete the jobs or maintenance plans in such cases from sysmaintplan_log table or from sysmaintplan_subplans table .
在sysmaintplan_log表或sysmaintplan_subplans表中,我们需要执行以下步骤来删除这种情况下的作业或维护计划。
Step 1: Execute the below query to obtain the Maintenance plan name and Id SELECT NAME, ID FROM MSDB..SYSMAINTPLAN_PLANS
步骤1:执行以下查询以获取维护计划名称和Id SELECT NAME,ID FROM MSDB..SYSMAINTPLAN_PLANS
(make sure msdb is selected for steps 2, 3 & 4)
(确保为步骤2,3和4选择了msdb)
Step 2: Replace the Id obtained from Step 1 into the below query and delete the entry from log table DELETE FROM SYSMAINTPLAN_LOG WHERE PLAN_ID=' '
步骤2:将从步骤1获得的Id替换为以下查询并从日志表中删除条目DELETE FROM SYSMAINTPLAN_LOG WHERE PLAN_ID =''
Step 3: Replace the Id obtained from Step 1 into the below query and delete the entry from subplans table as shown below, DELETE FROM SYSMAINTPLAN_SUBPLANS WHERE PLAN_ID = ' '
步骤3:将从步骤1获得的Id替换为以下查询,并从子计划表中删除该条目,如下所示,DELETE FROM SYSMAINTPLAN_SUBPLANS WHERE PLAN_ID =''
Step 4: Finally delete the maintenance plan using the below query where ID is obtained from Step1 DELETE FROM SYSMAINTPLAN_PLANS WHERE ID = ' '
步骤4:最后使用以下查询删除维护计划,其中ID来自Step1 DELETE FROM SYSMAINTPLAN_PLANS WHERE ID =''
Step 5: Check and delete the jobs from SSMS if it exists.
步骤5:检查并删除SSMS中的作业(如果存在)。
(I had to go into Jobs Activity Monitor and delete it from there.)
(我必须进入Jobs Activity Monitor并从那里删除它。)
Hope it helps u!
希望它能帮到你!
Kapil Khalas
#1
Thanks to SqlACID for the suggestion to fix this (see the question comments)
感谢SqlACID提出解决此问题的建议(请参阅问题评论)
I had been using Management Studio on a separate machine.
我一直在一台单独的机器上使用Management Studio。
Running Management Studio on the Server itself (and logging in as SA) allowed me to delete the stubborn Maintenance Plan.
在服务器本身上运行Management Studio(并以SA身份登录)允许我删除顽固的维护计划。
#2
I was not able to delete a Maintenance Plan because of a permission issue. "Authentication failed for user SA" - and I was logged in as SA using Management Studio on the server.
由于权限问题,我无法删除维护计划。 “用户SA的身份验证失败” - 我使用服务器上的Management Studio以SA身份登录。
Here is how I was able to fix the problem:
以下是我能够解决问题的方法:
- Open the problem Maintenance Plan.
- Add a new (empty) Subplan.
- Delete the first (existing) Subplan.
打开问题维护计划。
添加一个新的(空)子计划。
删除第一个(现有)子计划。
As a precaution I also did this:
作为预防措施,我也这样做了:
- Click the Manage Connections button.
- Click Edit in the Manage Connections dialog box.
- Overwrite the SA user name and password.
- Click OK > Close to get back to the Maintenance Plan.
单击“管理连接”按钮。
单击“管理连接”对话框中的“编辑”。
覆盖SA用户名和密码。
单击确定>关闭以返回维护计划。
Finally
- Save the Maintenance Plan.
- Delete the Maintenance Plan.
保存维护计划。
删除维护计划。
Hope this helps.
希望这可以帮助。
#3
I did not have success with Pete's suggestion, but I did have success following the steps here: https://social.msdn.microsoft.com/forums/sqlserver/en-US/ae6f809f-ef74-4534-8327-b7b120a2d700/unable-to-delete-a-sql-2005-maintenance-plan
我对Pete的建议没有成功,但我确实按照这里的步骤取得了成功:https://social.msdn.microsoft.com/forums/sqlserver/en-US/ae6f809f-ef74-4534-8327-b7b120a2d700/unable -to-删除-A-SQL-2005-维修计划
see the post by kahil kahalas:. it says:
看到kahil kahalas的帖子:它说:
You can also delete SQL 2005 Maintenance Plan using T - Sql.
您还可以使用T-Sql删除SQL 2005维护计划。
We need to perform the below steps to delete the jobs or maintenance plans in such cases from sysmaintplan_log table or from sysmaintplan_subplans table .
在sysmaintplan_log表或sysmaintplan_subplans表中,我们需要执行以下步骤来删除这种情况下的作业或维护计划。
Step 1: Execute the below query to obtain the Maintenance plan name and Id SELECT NAME, ID FROM MSDB..SYSMAINTPLAN_PLANS
步骤1:执行以下查询以获取维护计划名称和Id SELECT NAME,ID FROM MSDB..SYSMAINTPLAN_PLANS
(make sure msdb is selected for steps 2, 3 & 4)
(确保为步骤2,3和4选择了msdb)
Step 2: Replace the Id obtained from Step 1 into the below query and delete the entry from log table DELETE FROM SYSMAINTPLAN_LOG WHERE PLAN_ID=' '
步骤2:将从步骤1获得的Id替换为以下查询并从日志表中删除条目DELETE FROM SYSMAINTPLAN_LOG WHERE PLAN_ID =''
Step 3: Replace the Id obtained from Step 1 into the below query and delete the entry from subplans table as shown below, DELETE FROM SYSMAINTPLAN_SUBPLANS WHERE PLAN_ID = ' '
步骤3:将从步骤1获得的Id替换为以下查询,并从子计划表中删除该条目,如下所示,DELETE FROM SYSMAINTPLAN_SUBPLANS WHERE PLAN_ID =''
Step 4: Finally delete the maintenance plan using the below query where ID is obtained from Step1 DELETE FROM SYSMAINTPLAN_PLANS WHERE ID = ' '
步骤4:最后使用以下查询删除维护计划,其中ID来自Step1 DELETE FROM SYSMAINTPLAN_PLANS WHERE ID =''
Step 5: Check and delete the jobs from SSMS if it exists.
步骤5:检查并删除SSMS中的作业(如果存在)。
(I had to go into Jobs Activity Monitor and delete it from there.)
(我必须进入Jobs Activity Monitor并从那里删除它。)
Hope it helps u!
希望它能帮到你!
Kapil Khalas