SQL Account cannot access file system

Question

I have an issue with a service account that is used to import several files on its D: drive. In the past SA was used for this process, but of course it needed to be replaced by a service account. So I'm currently trying to find the correct permissions needed.

The user has r/w access to its tables and there is a file share (D:\Statistic) setup where "everyone" has read access.

Problem is the user cannot reach the location currently unless I make him a sysadmin.

To test the access outside of running the actual procedure I use:

EXEC Master.dbo.xp_fileexist 
'\\SRV1\Statistic\Statistic_2024.csv'

Bulkadmin or other roles do not suffice, and I had no luck with the server permissions so far. So in general, I'd guess the share is accessible but I'm missing a specific permission on SQL or system side that is only ignored due to specific exceptions in the sysadmin role.

Any ideas what I'm missing?

Answer 1

1. Instead of Master.dbo.xp_fileexist try

   SELECT * FROM sys.dm_os_file_exists('\SRV1\Statistic\Statistic_2024.csv');

2. Sign the stored procedure with sysadmins's certificate https://www.sommarskog.se/grantperm.html

Answer 2

The bulk insert is not able to access the file location, same with sys.dm_os_file_exists. So the general problem is the access to the file/directory.

The access to the directory is going to be based on the folder permissions provisioned to the SQL Server service account that your instance runs under. At the end of the day, that's what those types of operations get executed as.

If you're getting file locations access issues, then this has nothing to do with SQL Server permissions internally, or SysAdmin, or anything of that nature. You should provide the exact error message you're getting either way.