• 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);
    }
    };

  • 相关阅读:
    xshell下载官网地址
    logo
    网站案例搜集
    cnblogs修改网站图标icon
    父页面调用子页面方法, 子页面加载父页面传送的数据
    对Java的常用对象(POJO、DTO、PO、BO、VO、DAO)详细解释及应用场景
    关于jsp发起请求加载datagrid数据(草稿)
    接口测试-Http状态码-postman上传文件
    httpclient获取响应实体和信息的封装方法(解耦更新)
    使用httpClient调用接口获取响应数据
  • 原文地址:https://www.cnblogs.com/qanholas/p/1882223.html
Copyright © 2020-2023  润新知