sql clr自定义函数 程序集权限问题

--开启clr 
exec sp_configure 'show advanced options', '1' ; 
go 
reconfigure ; 
go 
exec sp_configure 'clr enabled', '1' 
go 
reconfigure ; 
exec sp_configure 'show advanced options', '1' ; 
go  

--创建cctv新用户，sa不行 
create login [cctv] with password=N'123456', default_database=[master], default_language=[简体中文], check_expiration=off, check_policy=off 
GO 
exec sys.sp_addsrvrolemember @loginame = N'cctv', @rolename = N'sysadmin' 
GO 
exec sys.sp_addsrvrolemember @loginame = N'cctv', @rolename = N'securityadmin' 
GO 
exec sys.sp_addsrvrolemember @loginame = N'cctv', @rolename = N'serveradmin' 
GO 
exec sys.sp_addsrvrolemember @loginame = N'cctv', @rolename = N'setupadmin' 
GO 
exec sys.sp_addsrvrolemember @loginame = N'cctv', @rolename = N'processadmin' 
GO 
exec sys.sp_addsrvrolemember @loginame = N'cctv', @rolename = N'diskadmin' 
GO 
exec sys.sp_addsrvrolemember @loginame = N'cctv', @rolename = N'dbcreator' 
GO 
exec sys.sp_addsrvrolemember @loginame = N'cctv', @rolename = N'bulkadmin' 
GO 



--授权程序集 
grant external access assembly to cctv 

grant unsafe assembly to cctv 

alter database qanholas set trustworthy on 


use qanholas 
go 
--创建程序集 
create assembly SqlClassLibrary  

from 'D:\SqlClassLibrary.dll'  

with permission_set = external_access  

--创建clr函数 
create function dbo.fun ( ) 
returns nvarchar(max) 
as external name  
    SqlClassLibrary.UserDefinedFunctions.Function1 


drop function fun 
select  dbo.fun()  --cctv用户执行，sa没有权限
 
-------------------------------------------- 
 
using System; 
using System.Data; 
using System.Data.SqlClient; 
using System.Data.SqlTypes; 
using Microsoft.SqlServer.Server; 
using System.IO; 
using System.Net; 

public partial class UserDefinedFunctions 
{ 
[Microsoft.SqlServer.Server.SqlFunction] 
public  static SqlString Function1() 
    { 
// 在此处放置代码 



        WebRequest request = WebRequest.Create(@"http://www.hao123.com/"); 
        WebResponse response = request.GetResponse(); 
        Stream resStream = response.GetResponseStream(); 
        StreamReader sr = new StreamReader(resStream, System.Text.Encoding.Default); 
        string htmlstr = sr.ReadToEnd(); 
        resStream.Close(); 
        sr.Close(); 

return new SqlString(htmlstr); 
    } 
};