SQLServer存储过程访问数据库权限异常问题的解决方案

最近用asp.net 2.0 + sql server做一个网页表单的提交，使用c#编写存储过程来完成向sql server数据库表中插入记录的操作。在调用这个存储过程时，出现了关于存储权限的一个异常。下面详述异常产生的过程和解决方案。

1.操作步骤：
1）使用asp.net 2.0，用c#写了一个存储过程，对数据库test中的一个表进行操作，代码如下：

public class storedprocedures
{
[microsoft.sqlserver.server.sqlprocedure]
public static void submit(string stranswer)
{
using (sqlconnection connection = new sqlconnection("data source=ws;initial catalog=test;integrated security=false;trusted_connection=yes;"))
{
connection.open();     // ***执行到这一步出现异常，详见下文所述***
string cmdtxt = "insert into dbo.response_scl90 values(" + stranswer + ")";
sqlcommand command = new sqlcommand(cmdtxt, connection);
sqldatareader reader = command.executereader();

}
}
}

2）编译生成了submit_answer.dll，接着，在sql server中注册这个dll，并创建存储过程，sql脚本如下：

create assembly submit_answer
from 'd:/study/c#/测评系统/website1/storedprocedure/submit_answer/bin/debug/submit_answer.dll';
go

create procedure dbo.submit_answer
(
@stranswer nvarchar(256)
)
with execute as owner
as
external name submit_answer.storedprocedures.submit
go

3）最后，在.net中调用这个存储过程，代码如下：
sqlconnection connection = new sqlconnection("data source=ws;initial catalog=test;integrated security=false;trusted_connection=yes;");

string cmdtxt = "dbo.submit_answer";
sqlcommand command = new sqlcommand(cmdtxt, connection);
command.commandtype = commandtype.storedprocedure;

command.parameters.add("@stranswer", sqldbtype.nvarchar);
command.parameters["@stranswer"].value = stranswer;

command.connection.open();
sqldatareader dr = command.executereader();

command.connection.close();

2. 异常描述：
在执行到存储过程 connection.open();一句时，出现异常，异常描述和当时的堆栈信息如下：
异常详细信息: system.data.sqlclient.sqlexception: a .net framework error occurred during execution of user defined routine or aggregate 'submit_answer':
system.security.securityexception: request for the permission of type 'system.data.sqlclient.sqlclientpermission, system.data, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089' failed.
system.security.securityexception:
at system.security.codeaccesssecurityengine.check(object demand, stackcrawlmark& stackmark, boolean ispermset)
at system.security.permissionset.demand()
at system.data.common.dbconnectionoptions.demandpermission()
at system.data.sqlclient.sqlconnection.permissiondemand()
at system.data.sqlclient.sqlconnectionfactory.permissiondemand(dbconnection outerconnection)
at system.data.providerbase.dbconnectionclosed.openconnection(dbconnection outerconnection, dbconnectionfactory connectionfactory)
at system.data.sqlclient.sqlconnection.open()
at storedprocedures.submit(sqlchars stranswer)

3.简要分析：
看来是在存储过程中没有对数据库的访问权限，因为在数据库连接open时就出错了，查找了一些资料，也没发现问题在哪。后来便在社区中提问了。

4.解决方案：
在“ms-sql server 疑难问题”版面，zlp321002(龙卷风2006)对这个问题进行解答，详情请见：
http://community.csdn.net/expert/topicview3.asp?id=4790457
现整理和总结如下：
（非常感谢zlp321002(龙卷风2006)，下面描述的解决方案源自zlp321002(龙卷风2006)）

1）打开数据库的外部访问选项（external_access_option）

alter database 数据库名
set trustworthy on

reference：关于数据库外部访问选项（external_access_option）的描述（摘录自sql server books online）

trustworthy { on | off }

    on

        database modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database.

    off

        database modules in an impersonation context cannot access resources outside the database.

    trustworthy is set to off whenever the database is attached.

    by default, the master database has trustworthy set to on. the model and tempdb databases always have trustworthy set to off, and the value cannot be changed for these databases.

    to set this option, requires membership in the sysadmin fixed server role.

    the status of this option can be determined by examining the is_trustworthy_on column in the sys.databases catalog view.

看来这个存储过程访问权限的解决是从下面两个方面进行：首先打开数据库的外部访问选项，允许数据库的模块访问外部资源；接着设置那个存储过程dll的permission_set，即设置这个dll的访问权限为允许访问外部资源。这样，存储过程就可以访问数据库了。