Unable to add SQLCLR assembly to database, Msg 300

Question

I'm having trouble adding the SQLCLR assembly to the database, but my colleague has no problem. Although we have different access levels, we cannot understand why I am getting the error message that I am receiving.

Here is my code:

USE [mydatabase] GO CREATE ASSEMBLY [My.Assembly] AUTHORIZATION [dbo] FROM 'C:\Program Files\MyStuff\My.Assembly.dll' WITH PERMISSION_SET = UNSAFE GO 

And here is my mistake:

Msg 300, Level 14, State 1, Line 3
UNSAFE ASSEMBLY permission was denied on the object "server", the database "master".

Thoughts?

Answer 1

I think you have a problem because the login is not part of sysadmin . MSDN says: "If PERMISSION_SET = UNSAFE is set, membership in the sysadmin fixed server role is required."

Answer 2

PLEASE do not add Login to sysadmin Fixed Server Role to get through this error. This is absolutely unnecessary!

The accepted answer is incorrect, not because it does not work (it is), but because there is no need to provide FULL CONTROL FOR FREE INSTANCE for logging in only to do what there is a specific permission for. You do not have to enter a domain administrator in Windows solely to give them permission to delete for a specific share or folder.

To be clear, this is not a poster error, as they correctly quoted MSDN documentation. The problem is that the MSDN documentation for CREATE ASSEMBLY was incorrect. The documentation for SQL Server 2008 R2 , unfortunately, indicated that Login should be in the sysadmin server sysadmin . However , it has since been corrected to indicate:

If PERMISSION_SET = UNSAFE is set, UNSAFE ASSEMBLY permission is required for permission on the server.

This permission, UNSAFE ASSEMBLY , is the exact permission indicated in the error message:

UNSAFE ASSEMBLY permission was denied on object 'server', database 'master'

Meaning, all that is needed is to do the following (at one time):

 USE [master]; GRANT UNSAFE ASSEMBLY TO [AD_domain_name\windows_login_name]; -- for Windows Logins 

or

 USE [master]; GRANT UNSAFE ASSEMBLY TO [sql_login_name]; -- for SQL Server Logins 

The reason you need to be in the [master] database is because this permission is the permission at the server level, not the database level, which should be applied to logins (which exist at the server level), not Users (which exist at the database level).

And therefore, the error message refers to object 'server' (because it is server-level permission) and database 'master' (since logins exist in the [master] database and can only be changed when the current database for the request is set to [master] ).

I tested this with Login, which will receive the error message indicated in the Question (i.e. Msg 300 ) when trying to load the assembly marked WITH PERMISSION_SET = UNSAFE . Then I granted UNSAFE ASSEMBLY permission, and Login was able to download the UNSAFE assembly; sysadmin membership is not required (or even attempt). I tested this on: SQL Server 2005 SP4, RTM, SQL Server 2008 R2 RTM, and SQL Server 2012 SP3.

Answer 3

Have you changed the database properties to establish reliability?

ALTER DATABASE Databasename SET TRUSTWORTHY ON;

From BOL Because a database attached to an instance of SQL Server cannot be trusted right away, the database is not allowed to access resources outside the database until the database is explicitly marked as trustworthy. In addition, modules designed to access resources outside the database, and assemblies with EXTERNAL_ACCESS and UNSAFE permission settings, have additional requirements for successful operation.