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

  • 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.

  • 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.

  • 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!

  • 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.

  • 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!

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

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