检查SQL Server 2005存储过程中是否存在文件的最佳方法是什么?

您必须将CLR标记为EXTERNAL_ACCESS才能访问系统。IO命名空间,然而,随着事情的发展,这并不是一个糟糕的方式。

SAFE是默认权限集,但限制性很强。使用SAFE设置,您只能访问本地数据库中的数据,以对该数据执行计算逻辑。 EXTERNAL_ACCESS是权限层次结构中的下一步。此设置允许您访问外部资源,如文件系统、Windows事件查看器和Web服务。这种类型的资源访问在SQL Server 2000及更早版本中是不可能的。此权限集还限制了影响程序集健壮性的指针访问等操作。 UNSAFE权限集假定程序集完全信任,因此没有“代码访问安全”限制。此设置与您假设所有代码都是安全的扩展存储过程功能的方式相当。但是,此设置确实将不安全程序集的创建限制为具有sysadmin权限的用户。Microsoft建议您尽可能避免创建不安全的程序集。

也许CLR存储过程就是您要找的。当您需要以某种方式与系统交互时,通常会使用这些功能。

我仍然相信CLR过程可能是最好的选择。所以,我接受这个答案。然而,要么我不是那么聪明,要么实施起来非常困难。我们的SQL Server服务在本地帐户下运行,因为根据Mircosoft的说法,这是使iSeries链接服务器从64位SQL Server 2005实例运行的唯一方法。当我们将SQL Server服务更改为使用域帐户运行时,xp_fileexist命令对位于网络上的文件工作良好。

我创建了这个CLR存储过程,并使用设置为“外部”的权限级别构建了它,并对其进行了签名:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Principal;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void FileExists(SqlString fileName, out SqlInt32 returnValue)
    {
        WindowsImpersonationContext originalContext = null;

        try
        {
            WindowsIdentity callerIdentity = SqlContext.WindowsIdentity;
            originalContext = callerIdentity.Impersonate();

            if (System.IO.File.Exists(Convert.ToString(fileName)))
            {
                returnValue = 1;
            }
            else
            {
                returnValue = 0;
            }
        }
        catch (Exception)
        {
            returnValue = -1;
        }
        finally
        {
            if (originalContext != null)
            {
                originalContext.Undo();
            }
        }
    }
}

然后我运行了这些TSQL命令:

USE master
GO
CREATE ASYMMETRIC KEY FileUtilitiesKey FROM EXECUTABLE FILE = 'J:\FileUtilities.dll' 
CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY FileUtilitiesKey 
GRANT EXTERNAL ACCESS ASSEMBLY TO CLRLogin 
ALTER DATABASE database SET TRUSTWORTHY ON;

然后,我从Visual Studio将CLR存储过程部署到目标数据库,并使用此TSQL从使用windows身份验证登录的Contoso执行:

DECLARE @i INT
--EXEC FileExists '\\\\server\\share\\folder\\file.dat', @i OUT
EXEC FileExists 'j:\\file.dat', @i OUT
SELECT @i

无论我尝试本地文件还是网络文件,我总是得到0。我以后可能会再试一次,但现在,我要试着走另一条路。如果有人能提供一些帮助,我们将不胜感激。