无法将 SQLCLR 程序集添加到数据库，Msg 300
我在将 SQLCLR 程序集添加到数据库时遇到问题，但我的同事没有问题。尽管我们确实有不同的访问级别，但我们无法弄清楚为什么我会收到这样的错误消息。
这是我的代码：
USE [mydatabase]
GO
CREATE ASSEMBLY [My.Assembly]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\MyStuff\My.Assembly.dll'
WITH PERMISSION_SET = UNSAFE
GO
这是我的错误：
消息 300，级别 14，状态 1，第 3 行
对象“服务器”、数据库“主”上的 UNSAFE ASSEMBLY 权限被拒绝。
想法？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
请不要将登录名添加到
sysadmin固定服务器角色，以解决此错误。完全没有必要！接受的答案是不正确的，不是因为它不起作用（它确实起作用），而是因为不需要将整个实例的完全控制权授予登录名，只是为了执行有特定权限的操作。您不会仅仅为了授予他们对特定共享或文件夹的删除权限而将 Windows 登录名设置为域管理员。
需要明确的是，这不是发布者的错，因为他们正确引用了 MSDN 文档。问题在于
CREATE ASSEMBLY的 MSDN 文档不正确。 SQL Server 2008 R2 文档 做到了不幸的是，声明登录必须位于sysadmin服务器角色中。但是，此后已更正以说明：此权限
UNSAFE ASSEMBLY与错误消息中所述的权限完全相同：含义是，所需要做的就是执行以下操作（一次）：
或者：
您需要位于
[master]数据库是这个权限是服务器级别的，而不是数据库级别的，需要应用于登录名（存在于服务器级别），而不是用户（存在于数据库级别）。这就是为什么错误消息引用
对象'server'（因为它是服务器级权限）和数据库'master'（因为登录存在于[master]数据库，并且仅当查询的当前数据库设置为[master]时才能修改）。我已经使用登录进行了测试，当尝试加载标记为
WITH PERMISSION_SET = UNSAFE的程序集时，该登录会收到问题中显示的错误消息（即Msg 300）。然后，我授予了UNSAFE ASSEMBLY权限，并且登录能够加载UNSAFE程序集；不需要（甚至尝试过）任何sysadmin会员资格。我对此进行了测试：SQL Server 2005 SP4、SQL Server 2008 R2 RTM 和 SQL Server 2012 SP3。PLEASE do not add a Login to the
sysadminFixed 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 ASSEMBLYwas incorrect. The documentation for SQL Server 2008 R2 did, unfortunately, state that the Login had to be in thesysadminServer Role. However, it has since been corrected to state:This permission,
UNSAFE ASSEMBLY, is the exact permission stated in the error message:Meaning, all that is needed is to do the following (one time):
or:
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) anddatabase '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 asWITH PERMISSION_SET = UNSAFE. I then granted thatUNSAFE ASSEMBLYpermission and the Login was able to load theUNSAFEAssembly; nosysadminmembership was required (or even attempted). I tested this on: SQL Server 2005 SP4, SQL Server 2008 R2 RTM, and SQL Server 2012 SP3.我认为您有问题，因为登录名不是
sysadmin的成员。 MSDN 说：“如果指定了 PERMISSION_SET = UNSAFE，则需要 sysadmin 固定服务器角色”更新
正如评论中提到的，可以而且应该在不将登录名分配给
sysadmin角色的情况下完成此操作。不幸的是，我无法删除这个答案，因为它已被接受，所以如果有人仍然拥有 SQLServer 2008 ，我建议参考 http:// /stackoverflow.com/a/38213540/577765 有详细解释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
sysadminrole . 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您是否更改了数据库属性以设置可信？
ALTER DATABASE 数据库名称 SET TRUSTWORTHY ON;
来自博尔
由于附加到 SQL Server 实例的数据库无法立即受信任，因此在将数据库显式标记为可信之前，不允许该数据库访问数据库范围之外的资源。此外，设计用于访问数据库外部资源的模块以及具有 EXTERNAL_ACCESS 和 UNSAFE 权限设置的程序集，为了成功运行还有其他要求。
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.