接上个帖子自动发布错误,手工发布一大段关于 PERMISSION_SET = EXTERNAL_ACCESS 的错误
CREATE ASSEMBLY [abc]
AUTHORIZATION [dbo]
FROM 'C:\...\abc.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
现在是 当前已经是 sa 了,如何授权?在管理器中直接添加,倒是成功了。然后用管理器生成脚本,成了下面这个样子
CREATE ASSEMBLY [CasterFeg3Kakuduke]
AUTHORIZATION [dbo]
FROM 0x4D5A9000030000...00000
WITH PERMISSION_SET = EXTERNAL_ACCESS
该如何生成可以到实装服务器发布的脚本?SQL CLR
CREATE ASSEMBLY [abc]
AUTHORIZATION [dbo]
FROM 'C:\...\abc.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
现在是 当前已经是 sa 了,如何授权?在管理器中直接添加,倒是成功了。然后用管理器生成脚本,成了下面这个样子
CREATE ASSEMBLY [CasterFeg3Kakuduke]
AUTHORIZATION [dbo]
FROM 0x4D5A9000030000...00000
WITH PERMISSION_SET = EXTERNAL_ACCESS
该如何生成可以到实装服务器发布的脚本?SQL CLR
CLR方式
a. 建立C#版的Classs Libary,函数如下:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
public partial class SQLSignature
{
// fn_SQLSigCLR
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString fn_SQLSigCLR(SqlString querystring)
{
return (SqlString)Regex.Replace(
querystring.Value,
@"([\s,(=<>!](?![^\]]+[\]]))(?:(?:(?:(?# expression coming
)(?:([N])?(')(?:[^']|'')*('))(?# character
)|(?:0x[\da-fA-F]*)(?# binary
)|(?:[-+]?(?:(?:[\d]*\.[\d]*|[\d]+)(?# precise number
)(?:[eE]?[\d]*)))(?# imprecise number
)|(?:[~]?[-+]?(?:[\d]+))(?# integer
))(?:[\s]?[\+\-\*\/\%\&\|\^][\s]?)?)+(?# operators
))",
@"$1$2$3#$4");
}
// fn_RegexReplace - for generic use of RegEx-based replace
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString fn_RegexReplace(
SqlString input, SqlString pattern, SqlString replacement)
{
return (SqlString)Regex.Replace(
input.Value, pattern.Value, replacement.Value);
}
}
b. 加载.dll中间语言代码到DB
USE master;
CREATE ASSEMBLY SQLSignature
FROM 'C:\SQLSignature\SQLSignature\bin\Debug\SQLSignature.dll';
c. 注册函数fn_SQLSigCLR和fn_RegexReplace
CREATE FUNCTION dbo.fn_SQLSigCLR(@querystring AS NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME SQLSignature.SQLSignature.fn_SQLSigCLR;
GOCREATE FUNCTION dbo.fn_RegexReplace(
@input AS NVARCHAR(MAX),
@pattern AS NVARCHAR(MAX),
@replacement AS NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME SQLSignature.SQLSignature.fn_RegexReplace;
GO
d. 注册完成之后,用下面代码测试:
SELECT
dbo.fn_SQLSigCLR(tsql_code) AS sig_sql,
duration
FROM dbo.Workload;
在创建程序集前,执行以下语句:
GRANT EXTERNAL ACCESS ASSEMBLY TO loginame
ALTER DATABASE dbname SET TRUSTWORTHY ON
(dbname 为准备创建的程序集所在的数据库,loginame 为该数据库的拥有者)
查询拥有者用户名
select suser_sname(owner_sid) from sys.databases where [name]='dbname'
来自msdn的注释
需要 CREATE ASSEMBLY 权限。如果指定 PERMISSION_SET = EXTERNAL_ACCESS,则 SQL Server 登录必须具有对服务器的 EXTERNAL ACCESS ASSEMBLY 权限。 如果指定 PERMISSION_SET = UNSAFE,则需要 sysadmin 固定服务器角色的成员身份。 如果程序集已经存在于数据库中,则用户必须是将上载的程序集所引用的所有程序集的所有者。 若要使用文件路径上载程序集,则当前用户必须是经过 Windows 身份验证的登录名或 sysadmin 固定服务器角色的成员。 执行 CREATE ASSEMBLY 的用户的 Windows 登录名必须对此语句中加载的共享和文件具有读取权限。
TRUSTWORTHY ON 已经设置了。