title: Determing file exists (yes/no) in SQL Server 2016
body: <p>My client just upgraded from SQL Server 2000 to SQL Server 2016. One stored procedure used 15-20 times per day runs through a table of file names and reports for each on whether it exists. </p>
  
  <p>Previously it used <code>xp_FileExist</code>, but it now seems to require admin rights to be used. I can't give all 176 users admin rights and keep my job.</p>
  
  <p>I found articles suggesting use of <code>GRANT IMPERSONATE</code> and <code>EXECUTE AS USER</code> but can't get it to work.</p>
  
  <pre><code>-- Old code works only for administrators
  -- non-administrators ALWAYS get 0 for result even when file exists
  
  EXEC master.dbo.xp_fileexist @docUNC, @FileExists OUTPUT
  
  -- code run using admin account
  
  SELECT SUSER_NAME() LOGIN_Name, USER_NAME() username;  
  </code></pre>
  
  <p>Results</p>
  
  <pre><code>LOGIN_Name                   username
  --------------------------------------
  DomainName\potomcandice      dbo
  
  -- attempted new code for grant impersonate (want to let maronj impersonate potomcandice)
  
  GRANT IMPERSONATE ON USER:: maronj TO potomcandice
  
  -- New code
  
  EXECUTE AS USER='potomcandice' 
  
  EXEC master.dbo.xp_fileexist @docUNC, @FileExists OUTPUT
  </code></pre>
  
  <p>Still get 0 for result even when the file does exist.</p>
  
  <p>Hoping for suggestions on either
  a) how to use "Grant Impersonate" and "Execute as User/Login" or
  b) alternatives to xp_fileexist that do not involve xp_commandshell.</p>