您如何克服这种错误:“不允许对系统目录进行临时更新."? [英] How do you get past this sort of error: "Ad hoc updates to system catalogs are not allowed."?

问题描述

输入以下内容:

update sys.assemblies set permission_set_desc = 'EXTERNAL_ACCESS' where
assembly_id = <someInt> and name not like 'microsoft%'

和以下输出:

Msg 259, Level 16, State 1, Line 2
Ad hoc updates to system catalogs are not allowed.

这是在 SQL Server 2012 中.我以sa"身份登录，所以这可能不是用户权限问题.我在 Google 上找到的链接要么没有解决方案，要么很难跟踪.如何在不对数据库产生不必要的永久性更改的情况下克服这个问题?谢谢！

This is in SQL Server 2012. I'm logged in as "sa", so this is probably not a user permissions issue. Links I'm finding on Google are either void of solutions or hard for me to follow. How do I get past this without producing unecessary permanent changes to the database? Thanks!

编辑

抱歉，我不小心从错误的窗口复制并粘贴了输入和输出.我删除了我真正遇到的问题，所以我将无法找到它是什么.第一条评论解决了我遇到的问题.我不记得现在到底是什么，但我很快从一件事转到另一件事......如果我记得我稍后在看什么，我会解决这个问题，但我会继续处理目前的问题.

Sorry, I accidentally copied and pasted input and output from the wrong window. I deleted what I was really having trouble with, so I'm not going to be able to find what it was. The first comment fixed whatever it was I having trouble with though. I don't remember what exactly it was now, but I was going from one thing to another pretty quickly...If I remember what I was looking at later, I'll fix the question, but I'll go ahead and handle the question as-is for now.

推荐答案

您无法更新系统目录，就像错误消息所说的那样.自 SQL Server 2000 以来，您一直无法做到这一点，即使在那些牛仔时代，这也很少是一个好主意.正如戈登所说，您需要这样做的方法是使用 ALTER组装.如果您只有一个程序集要更新:

You can't update the system catalogs, just like the error message said. You haven't been able to do this since SQL Server 2000, and even back in those cowboy days it was rarely a good idea. The way you need to do this, like Gordon said, is to use ALTER ASSEMBLY. If you only have a single assembly to update:

ALTER ASSEMBLY [assembly name] WITH PERMISSION_SET = EXTERNAL_ACCESS;

如果有多个，可以使用动态SQL生成脚本:

If you have multiple, you can generate a script using dynamic SQL:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'ALTER ASSEMBLY ' + QUOTENAME(name)
  + ' WITH PERMISSION_SET = EXTERNAL_ACCESS;
  '
FROM sys.assemblies WHERE assembly_id = <someInt>; -- or IN (<some range>)

PRINT @sql;
-- EXEC sp_executesql @sql;

如果您提供了特定的 assembly_id，我认为您不需要过滤掉 Microsoft 程序集.

I don't think you need to filter out the Microsoft assemblies if you've provided a specific assembly_id.

这篇关于您如何克服这种错误:“不允许对系统目录进行临时更新."?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！