--开启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);
}
};