How to change the CLR permission_set value from 3(unsafe) to 1(safe)

Question

How to change the CLR permission_set value from 3(unsafe) to 1(safe)

SQLisAwe5oMe

SSChampion

Points: 14047
More actions
March 8, 2017 at 9:59 am

#328178

SELECT name, is_user_defined, permission_set, permission_set_desc from sys.assemblies;
How can I update permission_set = 1 for the sys.assemblies system catalog view.
Currently the permission_set = 3 and which is unsafe and I need to change it to 1(safe).
Let me know if anyone know this....I tried googling, but couldn't find an answer.
Regards,
SQLisAwe5oMe.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 1

SQLisAwE5OmE - Wednesday, March 8, 2017 9:59 AM
SELECT name, is_user_defined, permission_set, permission_set_desc from sys.assemblies;
How can I update permission_set = 1 for the sys.assemblies system catalog view.
Currently the permission_set = 3 and which is unsafe and I need to change it to 1(safe).
Let me know if anyone know this....I tried googling, but couldn't find an answer.

This is SQL Server 2008 R2....FYI.

Regards,
SQLisAwe5oMe.

Lowell SSC Guru Points: 323504 More actions · Answer 2

I believe that when the CLR is being compiled, it assesses what it is doing, and has unalterables built in rules, depending on what the CLR does, which determines whether it can be considered safe or not.

so for things like pure string manipulation, like adding regular expressions, or other string stuff, you can build something that is safe.
if it touches a disk, so reading or writing, it's going to be EXTERNAL_ACCESS or maybe UNSAFE.
it really depends on what your CLR's are doing; is it a single CLR, or a collection of procs?

take a look at this for an overview:
https://msdn.microsoft.com/en-us/library/ms345101.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 3

Lowell - Wednesday, March 8, 2017 11:04 AM
I believe that when the CLR is being compiled, it assesses what it is doing, and has unalterables built in rules, depending on what the CLR does, which determines whether it can be considered safe or not.
so for things like pure string manipulation, like adding regular expressions, or other string stuff, you can build something that is safe.
if it touches a disk, so reading or writing, it's going to be EXTERNAL_ACCESS or maybe UNSAFE.
it really depends on what your CLR's are doing; is it a single CLR, or a collection of procs?
take a look at this for an overview:
https://msdn.microsoft.com/en-us/library/ms345101.aspx

Thanks Lowell, I am not sure if I am following everything you are saying.
I tried using sp_configure with 'allow update'.....but still was not able to update the column.

Regards,
SQLisAwe5oMe.

Lowell SSC Guru Points: 323504 More actions · Answer 4

you cannot, under any circumstances, change the value in SQL from UNSAFE to anything else.
you can remove and redesign features in your SQL CLR to NOT use unsafe methods, so that it successfully compiles and deploys as EXTERNAL_ACCESS or SAFE.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!