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?

Answer 1

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.

Answer 2

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 http://stackoverflow.com/a/38213540/577765 that has detailed explanation