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.
2）设置存储过程dll的PERMISSION_SET为EXTERNAL_ACCESS
将操作步骤第2）步中原来的
CREATE ASSEMBLY Submit_Answer
FROM 'D:\study\C#\测评系统\WebSite1\StoredProcedure\Submit_Answer\bin\Debug\Submit_Answer.dll'
GO
改为：
CREATE ASSEMBLY Submit_Answer
FROM 'D:\study\C#\测评系统\WebSite1\StoredProcedure\Submit_Answer\bin\Debug\Submit_Answer.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
reference：关于CREATE ASSEMBLY中PERMISSION_SET 设置（摘录自SQL Server Books Online）
PERMISSION_SET { SAFE | EXTERNAL_ACCESS | UNSAFE }
Specifies a set of code access permissions that are granted to the assembly when it is accessed by SQL Server. If not specified, SAFE is applied as the default.
We recommend using SAFE. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry.
EXTERNAL_ACCESS enables assemblies to access certain external system resources such as files, networks, environmental variables, and the registry.
UNSAFE enables assemblies unrestricted access to resources, both within and outside an instance of SQL Server. Code running from within an UNSAFE assembly can call unmanaged code.
Security Note:
SAFE is the recommended permission setting for assemblies that perform computation and data management tasks without accessing resources outside an instance of SQL Server. We recommend using EXTERNAL_ACCESS for assemblies that access resources outside of an instance of SQL Server. EXTERNAL_ACCESS assemblies include the reliability and scalability protections of SAFE assemblies, but from a security perspective are similar to UNSAFE assemblies. This is because code in EXTERNAL_ACCESS assemblies runs by default under the SQL Server service account and accesses external resources under that account, unless the code explicitly impersonates the caller. Therefore, permission to create EXTERNAL_ACCESS assemblies should be granted only to logins that are trusted to run code under the SQL Server service account. For more information about impersonation, see CLR Integration Security. Specifying UNSAFE enables the code in the assembly complete freedom to perform operations in the SQL Server process space that can potentially compromise the robustness of SQL Server. UNSAFE assemblies can also potentially subvert the security system of either SQL Server or the common language runtime. UNSAFE permissions should be granted only to highly trusted assemblies. Only members of the sysadmin fixed server role can create and alter UNSAFE assemblies.
做完上述修改后，再次运行网页，提交表单，不再出现异常了。
5. 小结：
看来这个存储过程访问权限的解决是从下面两个方面进行：首先打开数据库的外部访问选项，允许数据库的模块访问外部资源；接着设置那个存储过程dll的PERMISSION_SET，即设置这个dll的访问权限为允许访问外部资源。这样，存储过程就可以访问数据库了。
