Sql-server – How to grant a user UNSAFE ASSEMBLY permission

It would be something like this :

GRANT SELECT, INSERT, DELETE ON database TO username@'localhost' IDENTIFIED BY 'password';

To see a list of the privileges that have been granted to a specific user:

select * from mysql.user where User='username';

OR

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

An error in the form of:

Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id #####. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly '{_assembly_name_}, Version=#.#.#.#, Culture=neutral, PublicKeyToken=xxxxxxxxxxxxxxxx' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

means that the Assembly, which is currently marked with a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE is not allowed to make use of that permission level until the second part of the SQLCLR permissions setup is taken care of. That second part is to do one of the following:

• The very much preferred approach

  1. Sign the Assembly when compiling it (and give it a password!). This is sometimes referred to as giving it a Strong Name.

     If the Assembly isn't signed, and you don't have the source code to recompile it, and it doesn't have any other Assemblies that reference it, then you can still sign it by following the instructions in the first half of this blog post: http://ianpicknell.blogspot.com/2009/12/adding-strong-name-to-third-party.html

  2. Create an Asymmetric Key in [master] from the DLL of that Assembly
  3. Create a Login from that Asymmetric Key
  4. Grant that Login one of these two permissions: EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY (the UNSAFE ASSEMBLY permission also allows Assemblies to be marked as EXTERNAL_ACCESS, so you don't need both permissions)
  5. Create the Assembly in whichever Database(s) it should exist in
  6. Do NOT set the TRUSTWORTHY property of the Database(s) containing this Assembly to ON. TRUSTWORTHY can remain as OFF.

• The NOT preferred approach

  1. Alter the Database(s) where the Assembly should exist to be TRUSTWORTHY ON
  2. Make sure that the Login of the owner of the Database(s) containing this Assembly have one of these two permissions: EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY.
     
     If the owner of the Database(s) is sa, or any other Login that is in the sysadmin fixed server Role, then you don't need to worry about having one of these two permissions explicitly set as they are implied by the sysadmin Role.
  3. Try to avoid this approach, if at all possible :-)