Extended stored procedure xp_fileexist appears to require sysadmin permissions

Question

xp_fileexist appears to require sysadmin permissions.

If I run this with sysadmin

exec xp_fileexist '\servername\appfolder\filename.txt'

I get File Exist with a 1

However, when sysadmin permissions are removed it returns File Exists with a 0.

Does anyone know of any other way to grant the permission to return the correct result.

Answer 1

You can try

SELECT * FROM sys.dm_os_file_exists('\servername\appfolder\filename.txt');

Answer 2

I highly recommend against using undocumented extended procs or TSQL for file manipulation. They are not intended for user usage.

If you need to something on the file system, I suggest you use SSIS or SQL Agent.

Answer 3

I totally agree with @Tom Phillips .
You can also use CLR procedures.

https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/database-objects/getting-started-with-clr-integration?view=sql-server-ver15

Answer 4

I would agree with Hafeez that using sys.dm_os_file_exists is a better choice, since it is easier to use programmatically. But it is equally undocumented as xp_fileexists.

Nevertheless, there is a solution. Put the operation you want to conduct in a stored procedure, probably including at least the part of the folder to where the user can look for a file. That is, the user should not be able to interrogate the entire server.

Next you create a certificate and sign the procedure with the certificate. Then you create a login from the certificate and make that login member of sysadmin. That "login" is not a real login that can log in, it exists only to connect certificate and permission.

I discuss this technique in a lot more detail in the article (Packaging Permissions in Stored Procedures](https://www.sommarskog.se/grantperm.html) on my web site.

Answer 5

After discovering the same issue we used a mock bulk insert on the file. The login is required to be a member of the bulkadmin server role which is better than sysadmin (and it is perform a bulk admin anyway):

GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER procedure [Common].[usp_FileExists] (
    @pPathName nvarchar(300),
    @poExists bit out,
    @pDebug bit = 0
)    
with execute as 'LoginThatIsInBulkAdminRole'
as
/*---------------------------------------------------------------------------------------------------------------------
    Purpose:   Return true if the file exists.  Alternative to the xp_fileexists which may requires special permissions 
               for most users.
                  
    Modified    By              Description
    ----------  --------------  ---------------------------------------------------------------------------------------
    2020.04.08  crokusek        Initial version from udf_FileExists with added bulk insert test as a backup
  ---------------------------------------------------------------------------------------------------------------------*/ 
begin try
  
    declare
        @fileExists int;
        
    exec xp_fileexist @pPathName, @fileExists output;
    
    set @poExists = iif(coalesce(@fileExists, 0) = 1, 1, 0);
    
    if (@poExists = 1)
        return;
        
    -- else retest using a bulk insert as the above returns false negatives on 
    -- network drives that aren't physically the local machine and missing instance permission.
            
    begin try
        declare 
            @sql varchar(1000) = '
                declare @a bit = (
                    select 1 
                    from openrowset(bulk ''' + @pPathName + ''', single_blob) f
                );
            ';
            
        execute (@sql)        
        set @poExists = 1;  -- assume if no error that it worked
    end try    
    begin catch
        declare
            @errorMessage nvarchar(max) = error_message();
            
        if (@pDebug = 1)
            print 'Caught bulk test error: ' + @errorMessage;
        
        set @poExists = 0;
    end catch
    
    return @poExists;
end try
begin catch
    declare        
        @CatchingUsp  varchar(100) = object_name(@@procid);    
  
    if (xact_state() = -1)
        rollback;
    
  /*  Adjust as needed

   exec Common.usp_Log
        @pMethod = @CatchingUsp;
        
    exec Common.usp_RethrowError
        @pCatchingMethod = @CatchingUsp;*/
end catch

GO