最近用asp.net 2.0 + sql server做一個網(wǎng)頁表單的提交，使用c#編寫存儲過程來完成向sql server數(shù)據(jù)庫表中插入記錄的操作。在調(diào)用這個存儲過程時，出現(xiàn)了關(guān)于存儲權(quán)限的一個異常。下面詳述異常產(chǎn)生的過程和解決方案。

1.操作步驟：
1）使用asp.net 2.0，用c#寫了一個存儲過程，對數(shù)據(jù)庫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();     // ***執(zhí)行到這一步出現(xiàn)異常，詳見下文所述***
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，并創(chuàng)建存儲過程，sql腳本如下：

create assembly submit_answer
from 'd:/study/c#/測評系統(tǒng)/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中調(diào)用這個存儲過程，代碼如下：
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. 異常描述：
在執(zhí)行到存儲過程 connection.open();一句時，出現(xiàn)異常，異常描述和當時的堆棧信息如下：
異常詳細信息: 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.簡要分析：
看來是在存儲過程中沒有對數(shù)據(jù)庫的訪問權(quán)限，因為在數(shù)據(jù)庫連接open時就出錯了，查找了一些資料，也沒發(fā)現(xiàn)問題在哪。后來便在社區(qū)中提問了。

4.解決方案：
在“ms-sql server 疑難問題”版面，zlp321002(龍卷風2006)對這個問題進行解答，詳情請見：
http://community.csdn.net/expert/topicview3.asp?id=4790457
現(xiàn)整理和總結(jié)如下：
（非常感謝zlp321002(龍卷風2006)，下面描述的解決方案源自zlp321002(龍卷風2006)）

1）打開數(shù)據(jù)庫的外部訪問選項（external_access_option）

alter database 數(shù)據(jù)庫名
set trustworthy on

reference：關(guān)于數(shù)據(jù)庫外部訪問選項（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.

看來這個存儲過程訪問權(quán)限的解決是從下面兩個方面進行：首先打開數(shù)據(jù)庫的外部訪問選項，允許數(shù)據(jù)庫的模塊訪問外部資源；接著設(shè)置那個存儲過程dll的permission_set，即設(shè)置這個dll的訪問權(quán)限為允許訪問外部資源。這樣，存儲過程就可以訪問數(shù)據(jù)庫了。