首先要把数据库的TRUSTWORTHY属性改为ON
ALTER DATABASE [MYDB] SET TRUSTWORTHY ON GO
接下来直接创建ASSEMBLY应该就没问题了。但是往往有可能遇到一种情况 -- 在运行CREATE ASSEMBLY的时候发生错误
Msg 33009, Level 16, State 2, Line 5
The database owner SID recorded in the master database differs from the database owner SID recorded in database 'MYDB'. You should correct this situation by resetting the owner of database 'MYDB' using the ALTER AUTHORIZATION statement.
这句话其实就是告诉你目标数据库Owner的SIS和master数据库中记录的SID不一样。很可能的原因就是,因为我们需要把数据库还原到某一台测试机子上,然后因为login在不同的环境下SID不同(虽然名字相同)。解决办法就是把SID改成和master数据库中记录的SID一致
DECLARE @Command VARCHAR(MAX) = 'ALTER AUTHORIZATION ON DATABASE::<<DatabaseName>> TO [<<LoginName>>]' SELECT @Command = REPLACE(REPLACE(@Command , '<<DatabaseName>>', DB_NAME()) , '<<LoginName>>', sp.name) FROM sys.databases db JOIN sys.server_principals sp ON sp.sid = db.owner_sid WHERE db.name = 'master' --PRINT @Command EXEC(@Command) GO
然后就可以创建ASSEMBLY啦。
以之前博文中有一篇讲到的遍历整个文件目录的ASSEMBLY为例
USE [MYDB] GO CREATE ASSEMBLY [MYDB_CLR_Lib] AUTHORIZATION [dbo] FROM 'C:XXXXXX.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS GO
再创建存储过程
USE [MYDB] GO CREATE PROCEDURE [dbo].[usps_GetFileListByExtensionAndPattern] @SourceFolder [nvarchar](2000), @FileExtension [nvarchar](10), @FileNamePattern [nvarchar](2000), @IsSubfolderScanned [smallint] WITH EXECUTE AS CALLER AS EXTERNAL NAME [MYDB_CLR_Lib].[StoredProcedures.EnumerateSourceFileDirectory].[GetFileListByExtensionAndPattern] GO
还没完,还要把clr enabled服务器配置选项配成1
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'clr enabled', 1; GO RECONFIGURE; GO