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

  • 相关阅读:
    1242 斐波那契数列的第N项
    1256 乘法逆元
    1264 线段相交
    1265 四点共面
    Uva10881 Piotr's Ants
    hdu 5438 Ponds 长春网赛1002
    CodeForces 540D Bad Luck Island 概率dp
    hdu 1281 二分图残量增广
    hdu 2444判定二分图+最大匹配
    hdu 3416 Marriage Match IV
  • 原文地址:https://www.cnblogs.com/qanholas/p/1882223.html
Copyright © 2020-2023  润新知