SQL Server xp_fileExist different results depending on login

Question

I'm creating a stored procedure which needs to check if a file exists. It uses master.dbo.xp_fileExist to do this.

Problem is that xp_fileExist results seem to change based on the SQL Server login that I use. It seems to only work when the login has the SYSADMIN server role.

So, if I login using ACCOUNT:ACC1, PASSWORD:PWD1, and ACC1 does not have the SYSADMIN server role, I get the following results:

exec master.dbo.xp_fileExist 'adsnt.dll'

File Exists File is a Directory Parent Directory Exists
----------- ------------------- -----------------------
0           0                   0

If I then go into SSMS and assign the SYSADMIN role to account ACC1, I get the following results:

File Exists File is a Directory Parent Directory Exists
----------- ------------------- -----------------------
1           0                   1

I can't seem to find any documentation about this. Most responses to queries of this kind suggest that the login doesn't influence file access, and that instead it depends on the Service Account that SQL Server is running under. But clearly that's not the case (or, at least, it's not the complete story). Does anyone have any idea what's going on here, please?

Answer 1

-- Change this

exec master.dbo.xp_fileExist 'adsnt.dll'

-- To this
SELECT * FROM sys.dm_os_file_exists ('adsnt.dll')

Should work without the need for SysAdmin role