Cannot add a SQLCLR assembly to database, Msg 300

Question

I'm having trouble adding a SQLCLR assembly into a database, but a coworker of mine has no problem. Although we do have different levels of access, we can't figure out why I'm getting the error message that I'm getting.

Here's 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's my error:

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

Thoughts?

Brew

This would indicate that you are adding the Assembly to the master database. Is this correct? – Aila

Is the login you use a member of sysadmin role? – Betjeman

@Neil Knight: That's the problem, I don't know why it says "database 'master'". I have 'USE [mydatabase]' because I'm trying to insert the assembly into 'mydatabase' – Brew

If PERMISSION_SET = UNSAFE is specified, membership in the sysadmin fixed server role is required. – Aila

@NeilKnight and MStodd: No, the error message is not indicating that the Assembly is being added to the master DB, that is just where Logins are held and managed, and this is an issue of a server-level permission for the Login. Also, it was an MSDN documentation error that stated that membership in the sysadmin role was required. Adding someone to that role is a very bad idea. The permission needed is stated in the error message: UNSAFE ASSEMBLY. Please see my answer for full details :). – Doggone

Answer 1

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

Update

As it was mentioned in the comment, it could be and should be done without assigning logins to sysadmin role . Unfortunately , I can't delete this answer since it's accepted, so in case anyone still has SQLServer 2008 , I recommend to refer to https://mcmap.net/q/1516236/-cannot-add-a-sqlclr-assembly-to-database-msg-300 that has detailed explanation

Betjeman

This answer is quite incorrect, though not your fault as the MSDN documentation at that time was incorrect. Please see my answer for details. Please also place a note at the top of this answer directing people to not add Logins to the sysadmin server role as that is a huge security risk. – Doggone

Answer 2

PLEASE do not add a Login to the sysadmin Fixed Server Role in order to get past this error. It is absolutely not necessary!

The accepted answer is incorrect, not because it doesn't work (it does), but because there is no need to grant FULL CONTROL OVER THE ENTIRE INSTANCE to a Login just to do something that there is a specific permission for. You wouldn't make a Windows Login a Domain Admin solely for the purpose of giving them Delete permission on a particular share or folder.

To be clear, this isn't the poster's fault as they did correctly quote the MSDN documentation. The problem is that the MSDN documentation for CREATE ASSEMBLY was incorrect. The documentation for SQL Server 2008 R2 did, unfortunately, state that the Login had to be in the sysadmin Server Role. However, it has since been corrected to state:

If PERMISSION_SET = UNSAFE is specified, requires UNSAFE ASSEMBLY permission on the server.

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

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

Meaning, all that is needed is to do the following (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 that this permission is a Server-level, not Database-level, permission that needs to be applied to Logins (which exist at the Server-level), and not Users (which exist at the Database-level).

And this is why the error message references object 'server' (because it is a Server-level permission) and database 'master' (because Logins exist in the [master] Database and can only be modified when the current Database for the query is set to [master]).

I have tested this with a Login that would get the error message shown in the Question (i.e. Msg 300) when attempting to load an Assembly marked as WITH PERMISSION_SET = UNSAFE. I then granted that UNSAFE ASSEMBLY permission and the Login was able to load the UNSAFE Assembly; no sysadmin membership was required (or even attempted). I tested this on: SQL Server 2005 SP4, SQL Server 2008 R2 RTM, and SQL Server 2012 SP3.

Doggone

Answer 3

Have you altered the database properties to set trustworthy on?

ALTER DATABASE Databasename SET TRUSTWORTHY ON;

From BOL Because a database that is attached to an instance of SQL Server cannot be immediately trusted, the database is not allowed to access resources beyond the scope of the database until the database is explicitly marked trustworthy. Also, modules that are designed to access resources outside the database, and assemblies with either the EXTERNAL_ACCESS and UNSAFE permission setting, have additional requirements in order to run successfully.

Clarkin

TRUSTWORTHY has nothing to do with this error. The error message in the Question states what permission is missing. TRUSTWORTHY should not be set to ON unless absolutely necessary as it is a security risk. – Doggone