SQL Server ->> 在SQL Server中创建ASSEMBLY

首先要把数据库的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