1

Take the following input:

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

and the following output:

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

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!

EDIT

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.

Panzercrisis
  • 4,590
  • 6
  • 46
  • 85
  • 2
    Use commands like `ALTER ASSEMBLY` or `GRANT`. – Gordon Linoff Jan 17 '14 at 15:25
  • Down votes for no reason? It's not like I didn't look for this before posting it. – Panzercrisis Jan 17 '14 at 15:37
  • Wasn't me, but a guess might be about the research. The second hit for a Google search for that error message yielded [this question](http://stackoverflow.com/questions/15329764/ad-hoc-updates-to-system-catalogs-are-not-allowed), which clearly explains that you can't (or at least shouldn't, even if you're brave enough to try DAC in single-user mode) do this kind of thing with an UPDATE statement. – Aaron Bertrand Jan 17 '14 at 15:46
  • You're right...I copied and pasted the wrong input and output... – Panzercrisis Jan 17 '14 at 15:48

1 Answers1

5

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;

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;

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

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • I initially tried using "UNSAFE_ACCESS" because that's what the permission_set_desc value is set to, but it's actually just "UNSAFE" (as opposed to "EXTERNAL_ACCESS", which is that both in the description and when setting the PERMISSION_SET like in the query above). Ref: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-assembly-transact-sql?view=sql-server-2017 – Ryan Nov 26 '18 at 19:04